Compare two tables

[YANECKC]

I have two tables called sheet1 and sheet2.
The first column is Id-Code in both sheets.
I want to compare table(sheet1)Id-Code to table(sheet2) Id-Code and create three tables.
1. matched
2. unmatched sheet1
3. unmatched sheet2

Thank you for your anticipated response
Yaneckc@aol.com

 

[Anne Troy]

Have you tried using the query wizards? I believe you'll find matched and unmatched right there??

Good luck!

 

[jennyrave]

The unmatched should be easy by writing a select query. The other two you can hit insert, query, find unmatched query. Make them create a table if you wish.

 

[Anne Troy]

Sub DoIt()
CurrentDb.Execute ("INSERT INTO [unmatched sheet1] ( [Id-Code] ) SELECT
sheet2.[Id-Code] FROM sheet1 RIGHT JOIN sheet2 ON sheet1.[Id-Code] =
sheet2.[Id-Code] WHERE sheet1.[Id-Code] Is Null")
CurrentDb.Execute ("INSERT INTO [unmatched sheet2] ( [Id-Code] ) SELECT
sheet1.[Id-Code] FROM sheet2 RIGHT JOIN sheet1 ON sheet2.[Id-Code] =
sheet1.[Id-Code] WHERE sheet2.[Id-Code] Is Null")
CurrentDb.Execute ("INSERT INTO [matched] ( [Id-Code] ) SELECT
sheet1.[Id-Code] FROM sheet1 LEFT JOIN sheet2 ON sheet1.[Id-Code] =
sheet2.[Id-Code] WHERE Not sheet2.[Id-Code] Is Null")
End Sub

This code from our friendly neighborhood Batcoder.