What kind of query?

  • Thread starter Thread starter Nick Mirro
  • Start date Start date
N

Nick Mirro

I have two sets of taxonomic names. One contains 114 and the other 190000.
I need to know which from the smaller list are present in the larger list.
What sort of query and criteria would do this?

The larger list contains an autonumber primary key, and it is these "NodeID"
values I need returned. This way I can assign correct node numbers to the
smaller list and then add the 114 to the larger list. thanks.

Nick
 
Use a Find Duplicates query. Open a new query using the query wizard; it'll
guide you through the process.
 
Thanks for your help with this. This query seems directed to using small
queries or single tables for the data source. If I create a query that
contains the names from both lists (no relationship) I end up with 190,000
times 114 records - 21 million records! Its been running for quite a while
: ) Is there another way this can be done, or am I doing it wrong?

Nick
 
Dear Nick:

You are asking the computer to compare all 190,000 rows of one table
with all 114 rows in the other table. That truly is 20+ million
combinations.

Perhaps there is a key available in each of the two tables. If you
index and join them you could be done in seconds instead of hours.
Look for a field or set of fields that is unique of nearly unique,
then set up indexes on that.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Thanks. That makes sense to me now. I think I can do something along these
lines.

Nick
 
If you have a unique field common to both tables, set up a select query, add
both tables to the query, join both tables on that field by dragging a field
from one table onto the matching field in the other table. Include the
field from the large tables whose values you want returned if there's a
match. Run the query.
 
Back
Top