Comparing two tables in query and highlite the changes

[scotthris]

I have two very large tables with over 16000 record sets and I need to compare two of these to determine the differences. Once the differences are identified I need to highlite them and export to Excel format.

Can someone please give me the basic code to start this process as there are 93 fields in each record set?

Thanks for your help

[MMcCarthy]

SELECT A.[FieldName] FROM Table1 A
WHERE A.[FieldName] NOT IN (SELECT B.[FieldName]
FROM Table2 B)

This will return all records from A that don't have a corresponding value in the same field in B. Reversing it will do the opposite.

I have two very large tables with over 16000 record sets and I need to compare two of these to determine the differences. Once the differences are identified I need to highlite them and export to Excel format.

Can someone please give me the basic code to start this process as there are 93 fields in each record set?

Thanks for your help

[scotthris]

Thanks for the update, but the issue I see with this code is that it required a missing field.

What I am looking for are different values in the same field in two tables.

So 2 tables are exactly the same format but there has been some changes made in one table to the data stored in the fields. I need to be able to determine what field level data elements have been changed.

Thanks for any help.

[MMcCarthy]

That was just a sample code. If you give me the fieldnames and tablenames I will rewrite it for you

Thanks for the update, but the issue I see with this code is that it required a missing field.

What I am looking for are different values in the same field in two tables.

So 2 tables are exactly the same format but there has been some changes made in one table to the data stored in the fields. I need to be able to determine what field level data elements have been changed.

Thanks for any help.

[nimraj]

Hi,
Is there a way to compare two tables with 50+ fields and 1000+ records and find out the differences. The data and structure is similar. Someone could have made a change in any field in any record. A record could also have been added or removed. Just like any other text compare, can we write something in VBScript/query or module in Access to compare such tables ??