query to return only unique records from 2 tables?

[ranfree]

I have 2 tables (OLD and NEW) with identical formats, and I want to extract
only records where the ID is in the NEW table but is not in the OLD table.

for example:

OLD table:
ID fld1 fld2 fld3
1 any any any
3 any any any
5 any any any
6 any any any

NEW table
ID fld1 fld2 fld3
1 any any any
2 any any any
3 any any any
4 any any any
5 any any any
6 any any any
7 any any any

I want the following records returned:
ID fld1 fld2 fld3
2 any any any
4 any any any
7 any any any

Can you help me with a query or script to do this?

Thank you,

 

[Jason Lepack]

Try the "Find Unmatched" query wizard.

Cheers,
Jason Lepack

 

[Ofer Cohen]

You can use the Query Wizard to create an UnmatchRecord Query, that list all
the records in one table and not in the other one.

Something like

SELECT NewTableName.*
FROM NewTableName LEFT JOIN OldTableName ON NewTableName.ID= OldTableName.ID
WHERE OldTableName.ID Is Null