What kind of query?

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
 
K

Ken Snell

Use a Find Duplicates query. Open a new query using the query wizard; it'll
guide you through the process.
 
N

Nick Mirro

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
 
T

Tom Ellison

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
 
N

Nick Mirro

Thanks. That makes sense to me now. I think I can do something along these
lines.

Nick
 
R

Rolls

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.
 
Top