Comparing 2 fields

[ibeetb]

I want to write a query where two tables are joined on a common field and
then I want to compare, line by line, the differences in the fields that
make up each record. Example, they are joined on Project Number and then
compare where the PO number is different for the same project number from
both tables.....

Any help?

 

[Douglas J. Steele]

What exactly are you looking for: details of which rows aren't identical?

Join the two tables in the normal manner, and drag all of the fields from
Table 1 in the grid.

Ignore the Id field.

Under Field1, put <> [Table2].[Field1] for the criteria.

Under Field2, put <> [Table2].[FIeld2] ON A SEPARATE LINE for the criteria.

Repeat for all of the remaining fields, PUTTING EACH CRITERIA ON A SEPARATE
LINE IN THE GRID.

If you go into the SQL View of the query, you should see something like:

SELECT Table1.Id, Table1.Field1, Table1.Field2, Table1.Field3
FROM Table1 INNER JOIN Table2
ON Table1.Id = Table2.Id
WHERE Table1.Field1 <> Table2.Field1
OR Table1.Field2 <> Table2.Field2
OR Table1.Field3 <> Table2.Field3