Improve company productivity with a Business Account.Sign Up

x
?
Solved

MINUS (set diff.) operator in Access query

Posted on 2004-04-25
2
Medium Priority
?
4,317 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 200 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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This following write-up describes a different way to copy Lotus Notes Calendar to Outlook. Along with this, we will also learn the reason behind this NSF to PST migration. Users can prefer different procedures as per their convenience.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

589 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