View Full Version : Query to compare table differences


enllaw
06-27-2003, 01:10 PM
I need to run a query to compare any and all differences between two tables. This includes a row that has been deleted or added or any field that has been changed. I've tried to use the unmatched query wizard, but no differences are coming up. What criteria do I use for this?

Pat Hartman
06-27-2003, 01:21 PM
You actually need to do this in three parts:
1. Unmatched query A to B
2. Unmatched query B to A
3. Matched query with A inner joined to B and a where clause that compares the relevant fields -
Where A.fld1 <> B.fld1 OR A.fld2 <> B.fld2 OR A.fld3 <> B.fld3, etc.

If any of the fields might be null, the problem is compounded because you need to do independent tests for nulls in either table field (A.fld1 Is Null and B.fld1 Is Not Null) or (A.fld1 Is Not Null AND B.fld1 Is Null). If the contents of A.fld1 = "a" and the contents of B.fld1 is null, the condition A.fld1 <> B.fld1 will return FALSE rather than true as you would expect. That's why the specific null tests are required.