Unmatched data

[Guest]

Is there a way of easily comparing two tables and seeing whether any of the
same field data is different? I know how to create an Unmatched Records
Query to determine if Table A has the same records as Table B. However,
assuming both tables have the same records, I'd like to create a query that
tells me, for example, whether the phone number of Customer 123 in Table A is
different than the phone number of the same customer (123) in Table B.
Thanks.

 

[Marshall Barton]

Is there a way of easily comparing two tables and seeing whether any of the
same field data is different? I know how to create an Unmatched Records
Query to determine if Table A has the same records as Table B. However,
assuming both tables have the same records, I'd like to create a query that
tells me, for example, whether the phone number of Customer 123 in Table A is
different than the phone number of the same customer (123) in Table B.

I think this kind of query might get you started:

SELECT A.Customer, A.phone, B.phone
FROM [table A] As A INNER JOIN [table B] As B
ON A.Customer = B.Customer
WHERE A.phone <> B.phone

If that's close, then try to modify it to meet your other
needs.

 

[Guest]

Marshall,

Thanks for your reply. After re-reading my question, I realized I needed to
be a bit more specific. My table has 150 fields, so I don't want to write
150 queries. It seems to me that I once read somewhere that MS Access has
the ability to compare two identically-structured tables and indicate that
Record Key 123 of Table A is slightly different than record Key 123 of Table
B.

Ken

 

[Marshall Barton]

You probably need to rethink your table structure. A table
with more than a dozen (or two) fields raises a big red flag
that there is a normalization problem.

To compare multiple fields, just add more criteria:

SELECT A.Customer, A.phone, B.phone, . . .
FROM [table A] As A INNER JOIN [table B] As B
ON A.Customer = B.Customer
WHERE A.phone <> B.phone
OR A.thisfield <> B.thisfield
OR A.thatfield <> B.thatfield
OR A.otherfield <> B.otherfield
. . .

Regardless of normalization problems, you will probably hit
some kind of limit on how many fields you can check, but I
have never had a table with anywhere near that many fields
so I have not run into one.