• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Compare two tables and show uncommon data

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
gorony
Asked:
gorony
  • 7
  • 6
1 Solution
 
shanesuebsahakarnCommented:
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
 
goronyAuthor Commented:
OK, off to figure out how to do direct SQL language in Access ;)
I'll let you know how it works out.
Thanks!
0
 
shanesuebsahakarnCommented:
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
goronyAuthor Commented:
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
 
shanesuebsahakarnCommented:
Yep, that's right - is that what you are getting when you run the query?

Bitte :)
0
 
goronyAuthor Commented:
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
 
goronyAuthor Commented:
<sigh> I mean right join in the select statements above....
0
 
shanesuebsahakarnCommented:
Sorry - is is this the query that works or doesn't work?
0
 
goronyAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
goronyAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
goronyAuthor Commented:
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now