Solved

MINUS (set diff.) operator in Access query

Posted on 2004-04-25
2
4,282 Views
Last Modified: 2007-12-19
I have to retrive records from a table that are not present in another table with the same schema.  But I am working with access database and as much I know it doesn't support MINUS operator.  I use following sql query instead.

SELECT name FROM ref  WHERE not exists (SELECT name FROM glossary WHERE ref.name=glossary.name)

ref has 6571 records
glossary has 11315 records

It's extremely slow.  It took about 2'48'' to return 124 records on a PIII 866 machine.

Is there any other good way?

Thanks in advance.

Jerry
0
Comment
Question by:Jerryleo
2 Comments
 
LVL 6

Accepted Solution

by:
nathaniel earned 50 total points
ID: 10915606
say you have a ref table with (ID, name, file)
and glossary table (ID, name, page)

the faster way to identify is like this example:

SELECT ref.name, FROM ref LEFT JOIN glossary ON ref.ID = glossary.ID
WHERE (glossary.name) Is Null;

this will display all the names from ref table that are not found in glossary table, based on their ID. You can also use the 'name' in joining, instead of their IDs.

hope that helps

0
 

Author Comment

by:Jerryleo
ID: 10924175
Thank you very much

It works great. It only took 0.5' on my PIII 866 machine.

Best Regard

Jerry
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Dateadd 3 22
Access MDB/PDF 21 32
Code editor Problem 8 16
Dlookup issue 4 15
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now