Solved

Compare two tables and show uncommon data

Posted on 2004-09-09
13
321 Views
Last Modified: 2008-01-16
Greetings,

I've got 2 tables in a database, both have the same structure.  One table is from month X, one is from month Y.  I'm trying to figure out how to set up a query with the queries function (ether design or wizard) will output a third table, that table containing only data records which do not appear in both the source tables (basically, new entries in month Y).  The fields are "first & middle name", "last name & suffix", "street 1", "street 2", "city", "state", "zip code" - standard mailing list stuff.  None of those fields is the primary key, that is a field called "id" which appears to be unique, but I don't know what it is keyed from.

Thanks in advance
Peter
0
Comment
Question by:gorony
[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
  • 7
  • 6
13 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12022570
To find all the records in table a but not table b, use something like this:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.LastName = TableB.LastName AND TableA.FirstName=TableB.FirstName WHERE TableA.LastName Is Null

add as many AND clauses in there as you need to match fields. Now you can do the same to find records in B but not A by reversing the table names:

SELECT * FROM TableB LEFT JOIN TableA ON TableB.LastName = TableA.LastName AND TableB.FirstName=TableA.FirstName WHERE TableB.LastName Is Null

UNIONing these two together gives you records that aren't in both tables:

SELECT * FROM TableA LEFT JOIN TableB ON TableA.LastName = TableB.LastName AND TableA.FirstName=TableB.FirstName WHERE TableA.LastName Is Null
UNION ALL
SELECT * FROM TableB LEFT JOIN TableA ON TableB.LastName = TableA.LastName AND TableB.FirstName=TableA.FirstName WHERE TableB.LastName Is Null

Now just turn this into a make-table query and there you go.
0
 

Author Comment

by:gorony
ID: 12022619
OK, off to figure out how to do direct SQL language in Access ;)
I'll let you know how it works out.
Thanks!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12022639
Design a query and then click View->SQL View to write SQL queries.

Don't forget to close this question when you've achieved what you need :-)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:gorony
ID: 12022705
Greetings,

OK, to test this out I made 2 tables, TableA and B with 3 fields: firstname, lastname, and address, then created 3 records each.  2 records exist in both tables, and 1 record is unique to each table, created a query and pasted the union section above into the SQL Query view.  Should I then have a data sheet view with 2 records showing?

Danke!
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12022712
Yep, that's right - is that what you are getting when you run the query?

Bitte :)
0
 

Author Comment

by:gorony
ID: 12022771
Greetings,

OK,   for some reason it comes up with records if I do it as a right-join but if it's a left-join the query returns no records.  So, now my query is:

SELECT * INTO TableC FROM TableA LEFT JOIN TableB ON TableA.LastName = TableB.LastName AND TableA.FirstName=TableB.FirstName WHERE TableA.LastName Is Null
UNION ALL
SELECT * INTO TableC FROM TableB LEFT JOIN TableA ON TableB.LastName = TableA.LastName AND TableB.FirstName=TableA.FirstName WHERE TableB.LastName Is Null;
0
 

Author Comment

by:gorony
ID: 12022782
<sigh> I mean right join in the select statements above....
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12022794
Sorry - is is this the query that works or doesn't work?
0
 

Author Comment

by:gorony
ID: 12022802
The query that works is:

SELECT *  FROM TableA RIGHT JOIN TableB ON TableA.LastName = TableB.LastName AND TableA.FirstName=TableB.FirstName WHERE TableA.LastName Is Null
UNION ALL SELECT * FROM TableB RIGHT JOIN TableA ON TableB.LastName = TableA.LastName AND TableB.FirstName=TableA.FirstName WHERE TableB.LastName Is Null;

Is it strange that the join direction matters?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12022811
Hmm, that's odd - changing the join direction should be exactly the same as swapping the two tables around and making it a left join - but we do that anyway because of the two different queries!

Oh well, if it works, let's not complain :)
0
 

Author Comment

by:gorony
ID: 12022822
Yea, definitely ;)

Now, changed the 1st segment to:  "SELECT * INTO TableC FROM TableA <etc>" to try and output into a new table, and get "Action query cannot be used as a row source" (Um, sensing that I've never used Access before? :) )
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 350 total points
ID: 12022833
Best thing to do is save the UNION query, and create a new query that uses the union query as a source. The new query can be turned into a make-table (you can do it in a single statement but it's a bit of a pain).
0
 

Author Comment

by:gorony
ID: 12022906
Hokey, created Query2 and it now grabs the 3 records and creates a new table, perfect.  Now to hack it to the real databases, but that's a job for tomorrow :)

Thanks for all the quick help, bumping the points and accepting.

Peter
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

687 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