[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

MINUS (set diff.) operator in Access query

Posted on 2004-04-25
2
Medium Priority
?
4,305 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

656 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