Compare Data in 2 Tables

[JAD814]

Forgive me if I've missed the answer to this elsewhere in these forums, and I have read things that seem CLOSE to what I want, but not exactly...

Basically, I have 2 tables that for the sake of simplicity look like this...

Table1
------
Column1(PK)
Column2
Column3
Column4

Table2
------
Column1(PK)
Column2
Column3
Column4

What I want, is to return rows where data differs, that is (Table1.Column2 <> Table2.Column2, Table1.Column3 <> Table2.Column2, etc). Logically, what I want (since the real life usage for this needs to compare the data in 14 different columns) is to somehow return (possibly via expression, although I get foggy when that comes into play) the ColumnName where the data differs, so that I don't have to physically look at the row and manually see what field made one of the 14 conditions true.

I don't know if this is possible, but even if the query didn't return the mismatching column name, it'd still be a TREMENDOUS help!

I apologize for the terrible wording of this thread, but I don't know an easy way to explain this one!

Thanks in advance for any and all help!

 

[dhookom]

You can try create a union query that will find and present all the columns that are different in a separate record of the output like:

SELECT Column1, 2 as ColNumber, Table1.Column2 as Value1, Table2.Column2 as Value2
FROM Table1 INNER JOIN Table2 on Table1.Column1 = Table2.Column1
WHERE Table1.Column2 <> tabl2.Column2
UNION ALL
SELECT Column1, 3, Table1.Column3, Table2.Column3
FROM Table1 INNER JOIN Table2 on Table1.Column1 = Table2.Column1
WHERE Table1.Column3 <> tabl2.Column3
UNION ALL
SELECT Column1, 4, Table1.Column4, Table2.Column4
FROM Table1 INNER JOIN Table2 on Table1.Column1 = Table2.Column1
WHERE Table1.Column4 <> tabl2.Column4
UNION ALL
--- repeat until ---
SELECT Column1, 14, Table1.Column14, Table2.Column14
FROM Table1 INNER JOIN Table2 on Table1.Column1 = Table2.Column1
WHERE Table1.Column14 <> tabl2.Column14;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]