Comparison Query

[mkc80]

I need to build a query where I can output only the columns which do not match in two different tables A and B (which contain same number of columns and similar information)


For example table A and B both have 10 columns with same 3 primary keys.
(Reason for doing this is to find data entry errors in either table A or B)

[June7]

Tables are 'similar' - how are they different?

What are you looking for - errors in primary keys? Did you try Find Unmatched query?

[mkc80]

Not errors in primary keys. Data entry errors in the remaining 7 columns. Structure of the two tables is exactly the same.

For example table A has the values:
A B C 1 2 3 4 5 6 7 (this is one row, A B C are the values for primary keys)
Table B has the values
A B C 1 2 3 4 5 6 8 (this is one row, A B C are the values for primary keys)

As you can see the 10th column has different values (7 in table A, 8 in table B).
The result of the query should be Columns 1,2,3 & 10, along with the values in table B.

[June7]

So the same primary key values are in both tables - i.e. the same values and same number of records so there is a one-to-one relationship?

Create a query that joins the two tables on the 3 keys. Pull in the fields from both tables then create 7 fields with expressions like:
Col1: TableA.fieldname1=TableB.fieldname1
Then in criteria row under each constructed field: =False
Put each criteria on a separate row so get stair-step effect and the OR operator is used.
These constructed fields can be not visible by unchecking Show row.

Records where any field pair does not match will be returned. You will have to see all the native fields. Arrange to display the pairs together so you can examine the variances.