Solved

Compare two tables and show uncommon data

Posted on 2004-09-09
13
318 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

830 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