Solved

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

Posted on 2011-02-21
12
1,335 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
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.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about DB Schema 27 56
Querying data from 3 SQL tables 2 32
What's wrong with this T-SQL Foreign Key? 7 45
is Microsoft Access going to Die? 9 47
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
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.

773 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