Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-21
12
Medium Priority
?
1,341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +1
12 Comments
 
LVL 93

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 85
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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 44

Expert Comment

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

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 93

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 85
ID: 34944195
Sorry ... you are right. I apparently did no process this one correctly in my befuddled brain!

0
 
LVL 93

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 93

Accepted Solution

by:
Patrick Matthews earned 320 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 93

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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