Go Premium for a chance to win a PS4. Enter to Win

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

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

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
datasolutionz
Asked:
datasolutionz
  • 6
  • 2
  • 2
  • +1
1 Solution
 
Patrick MatthewsCommented:
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
 
GRayLCommented:
And that, I believe, is what the Unmatched Query wizard will create.  Something else is amiss.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
GRayLCommented:
The AND will take care of that Scott.
0
 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Sorry ... you are right. I apparently did no process this one correctly in my befuddled brain!

0
 
Patrick MatthewsCommented:
:)

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
 
datasolutionzAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
datasolutionzAuthor Commented:
Works great, Matthewspatrick...you're awesome!

LSM Consulting...thanks for your attempt as well.!
0
 
Patrick MatthewsCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now