Solved

Compare two tables and show uncommon data

Posted on 2004-09-09
13
315 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:gorony
Comment Utility
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
Comment Utility
Yep, that's right - is that what you are getting when you run the query?

Bitte :)
0
 

Author Comment

by:gorony
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

Expert Comment

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

Author Comment

by:gorony
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

728 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

14 Experts available now in Live!

Get 1:1 Help Now