extracting records

  • Thread starter Thread starter jochen scheire
  • Start date Start date
J

jochen scheire

Hello,

I have two tables in a database. One table with about 3000 names, another
one with 500 names. I want to create a third table with 2500 names, being
the 3000 from the one table, minus the 500 names from the other table, how
can you do this?
 
Dear Jochen:

I'm not sure you can assume all 500 names are included in the 3000.
There may not be exactly the 2500 you expect if this is not the case.

SELECT T1.[name]
FROM YourTable1 T1
INNER JOIN YourTable2 T2 ON T2.[name] = T1.[name]

This will give you the 2500.

What you have here is the intersection of two sets. There are two
other regions of interest (potentially): those names in the 3000 NOT
in the 500, and those in the 500 not in the 3000. You should probably
look at these as well to test your intentions against actuality:

SELECT T1.[name]
FROM YourTable1 T1
LEFT JOIN YourTable2 T2 ON T2.[name] = T1.[name]
WHERE T2.[name] IS NULL

SELECT T1.[name]
FROM YourTable2 T1
LEFT JOIN YourTable1 T2 ON T2.[name] = T1.[name]
WHERE T2.[name] IS NULL

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top