Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 151
  • Last Modified:

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?
0
Jay Williams
Asked:
Jay Williams
1 Solution
 
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
 
Rey Obrero (Capricorn1)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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now