Solved

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

Posted on 2015-02-18
6
130 Views
Last Modified: 2015-02-18
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
Comment
Question by:Jay Williams
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 40617196
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
 

Author Comment

by:Jay Williams
ID: 40617204
Thanks, Kevin.  I'll give it a whirl.
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 40617215
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 40617230
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
 

Author Comment

by:Jay Williams
ID: 40617288
Thanks again, Rey.  That's what I have in mind.  I'll let you know. :-)
0
 

Author Closing Comment

by:Jay Williams
ID: 40617325
This is pure gold--and so are you.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now