Solved

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

Posted on 2011-02-21
12
1,334 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
 
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
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

867 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

12 Experts available now in Live!

Get 1:1 Help Now