Solved

SQL query to find unmatched items between 2 tables in Microsoft Access

Posted on 2011-02-21
12
1,336 Views
Last Modified: 2012-08-14
In my Microsoft Access 2007 database,  have a 2 Tables, tblA & tblB. Each has columns FirstName & LastName. I would like to be able to compare the combination of FirstName & LastName columns in both tables & find out which combinations of FirstName & LastName in tblA are not present in tblB. I have tried using the Unmatched query wizard but keep getting an empty recordset, however, I know there are combinations of FirstName & LastName in tblA that are not present in tblB. Thanks for your help & attention.
0
Comment
Question by:datasolutionz
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34943881
SELECT a.*
FROM tblA a LEFT JOIN
    tblB b ON a.FirstName = b.FirstName And a.LastName = b.LastName
WHERE b.FirstName Is Null
0
 
LVL 44

Expert Comment

by:GRayL
ID: 34944003
And that, I believe, is what the Unmatched Query wizard will create.  Something else is amiss.
0
 
LVL 84
ID: 34944025
It would seem that you'd need to check for the LASTNAME as well:

SELECT a.*
FROM tblA a LEFT JOIN
    tblB b ON a.FirstName = b.FirstName And a.LastName = b.LastName
WHERE b.FirstName Is Null OR b.LastName IS NULL
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 44

Expert Comment

by:GRayL
ID: 34944080
The AND will take care of that Scott.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34944131
LSM,

I doubt that's it.  Assume tblA has:

FirstName    LastName
-------------------------
Alfred       Adams
Bob          Boone
Charlie      Chan
David        Dandridge

Open in new window


and tblB has:

FirstName    LastName
-------------------------
Amanda       Adams
Bob          Bigglesworth
Charlie      Chan
Daniel       Devlin

Open in new window



Running my query yields:

FirstName    LastName
-------------------------
Alfred       Adams
Bob          Boone
David        Dandridge

Open in new window


Which is the expected result :)
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34944163
Logically, you should only have to test for one null in this case: since the join specifies both columns, if both columns fail the match, then the whole row fails, and thus all of the corresponding results from tblB will be null, even if there was a partial match.
0
 
LVL 84
ID: 34944195
Sorry ... you are right. I apparently did no process this one correctly in my befuddled brain!

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34944275
:)

BTW, my comment above should have been:

Logically, you should only have to test for one null in this case: since the join specifies both columns, if either or both columns fail the match, then the whole row fails, and thus all of the corresponding results from tblB will be null, even if there was a partial match.
0
 

Author Comment

by:datasolutionz
ID: 34944645
All, Thanks for the input. Sorry, I forgot to further clarify. So tblA can have multiple records that are same, hence, when I need to compare tblA & tblB (lookup table with unique records), I really need to be comparing DISTINCT rows (combinations of FirstName & LastName) from tblA against tblB.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 80 total points
ID: 34944705
Not sure if DISTINCT works with *.  If it does:



SELECT DISTINCT a.*
FROM tblA a LEFT JOIN
    tblB b ON a.FirstName = b.FirstName And a.LastName = b.LastName
WHERE b.FirstName Is Null 

Open in new window




If not:


SELECT DISTINCT a.FirstName, a.LastName
FROM tblA a LEFT JOIN
    tblB b ON a.FirstName = b.FirstName And a.LastName = b.LastName
WHERE b.FirstName Is Null 

Open in new window

0
 

Author Comment

by:datasolutionz
ID: 34944799
Works great, Matthewspatrick...you're awesome!

LSM Consulting...thanks for your attempt as well.!
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 34944808
Access will accept

SELECT DISTINCT a.*
FROM tblA a LEFT JOIN
    tblB b ON a.FirstName = b.FirstName And a.LastName = b.LastName
WHERE b.FirstName Is Null 

Open in new window


as valid syntax.  That said,

SELECT DISTINCT a.FirstName, a.LastName
FROM tblA a LEFT JOIN
    tblB b ON a.FirstName = b.FirstName And a.LastName = b.LastName
WHERE b.FirstName Is Null 

Open in new window


may still be more appropriate, if all you care about are the FirstName and LastName, but tblA has other columns as well...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.

821 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