Compare Multiple Fields in Two Tables and Show Whats Different

[KIMA06]

All,

Here is what I am dealing with. I have two tables: West(NY) and South (NY).
These tables are supposed to be identical, however the person that updates
South(NY) messed up big time and has added more information than what should
be in the table.

Both tables are built the same and have the same column headings (for the
sake of this query, I will call them Field1, Field2, Field3, and Field 4. I
need to use West(NY) table as the master table, and I need to run a query
that will show me what is in the South(NY) table that is different. For
instance lets just say that in South(NY) Table that Field2 has "Farmingdale
(916)". However in the West(NY) table, Field2 is just "Farmingdale".

The unmatched query is not working, so if you have an SQL statement that I
can play with, it will be greatly appreciated. Thanks in advance.

 

[Douglas J. Steele]

SELECT Table1.Field1, Table2.Field2, Table1.FIeld2, Table2.Field2,
"Different" As Comment
FROM Table1 INNER JOIN Table2
ON Table1.Id = Table2.Id
WHERE Nz(Table1.Field1) <> Nz(Table2.Field1)
OR Nz(Table1.Field2) <> Nz(Table2.Field2)
UNION
SELECT Table1.Field1, Null, Table1.FIeld2, Null, "In Table1, Not in Table2"
As Comment
FROM Table1 LEFT JOIN Table2
ON Table1.Id = Table2.Id
WHERE Table2.Id IS NULL
UNION
SELECT Null, Table2.Field2, Null, Table2.Field2, "In Table2, Not in Table1"
As Comment
FROM Table2 LEFT JOIN Table1
ON Table1.Id = Table2.Id
WHERE Table1.Id IS NULL

 

[KIMA06]

Doud,

Thanks for the response. The SQL statement met my needs. If I have any
further questions, believe me I will not be a stranger. Thanks again.

Karim