Solved

Compare two tables and show uncommon data

Posted on 2004-09-09
13
316 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
  • 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: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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