In Access 2010 How can I find records in one table that have no exact match in another table?

I need to find records that do not match by multiple fields.  What's a good strategy, or is there some generic SQL statements that might give me a clue?
Jay WilliamsOwnerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
if you want to test for multiple fields

SELECT Table1.F1, Table1.F2, Table1.F3, Table1.F4, Table2.F1, Table2.F2, Table2.F3, Table2.F4
FROM Table1 LEFT JOIN Table2 ON (Table1.F4 = Table2.F4) AND (Table1.F3 = Table2.F3) AND (Table1.F2 = Table2.F2) AND (Table1.F1 = Table2.F1)
WHERE (((Table2.F1) Is Null) AND ((Table2.F2) Is Null) AND ((Table2.F3) Is Null) AND ((Table2.F4) Is Null));
0
 
Kelvin SparksCommented:
SELECT a.*
FROM a LEFT JOIN b ON
b.primarykey = a.primarykey
WHERE a.primarykey is not null and b.primarykey is null

Will give you all records in a that don't have a match in b.

Kelvin
0
 
Jay WilliamsOwnerAuthor Commented:
Thanks, Kevin.  I'll give it a whirl.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Dale FyeCommented:
One of the query wizard options is a "Find Unmatched" query, which will walk you through the steps of identifying which field(s) you want to match on, and will allow you to select other fields from the table as well.
0
 
Jay WilliamsOwnerAuthor Commented:
Thanks again, Rey.  That's what I have in mind.  I'll let you know. :-)
0
 
Jay WilliamsOwnerAuthor Commented:
This is pure gold--and so are you.
0
All Courses

From novice to tech pro — start learning today.