Field by Field Comparison

[Chris]

I have two tables with the exact same column names. I want to do a field by
field comparison of the two tables. If one letter or number is off in any
field, I would like for the whole record to show up in my query. I can only
figure out how to show records which are equal. I would like to show records
which are not equal.

Is there a query criteria or something where I can perform this?

Thanks.

 

[Jerry Whittle]

Do a UNION query.

Create a query that lists all the field in one table. Then open that query
in SQL view.

At the end of the SQL, delete the semicolon ";".

At the next line down put UNION .

Then copy the SQL above the word UNION and past it under the word UNION.

Change the table and field names as needed to match the other table.

When you run this query, it should only return one instance of a record that
exactly matches in both tables. If there are any differences, it will return
all the unique records.

 

[John W. Vinson]

I have two tables with the exact same column names. I want to do a field by
field comparison of the two tables. If one letter or number is off in any
field, I would like for the whole record to show up in my query. I can only
figure out how to show records which are equal. I would like to show records
which are not equal.

Is there a query criteria or something where I can perform this?

Thanks.

Jerry's UNION query will work, but will require that you dig through the
records looking for the mismatches.

Is there any Primary Key in the tables, or any field which would let you say
that "this record goes with that record"? Otherwise, if you have 1000 records
in TableA and 1500 records in TableB, you would need to compare all 1,500,000
combinations... or are you (incorrectly) assuming that tables have record
numbers, and that there is a "fifteenth record" in TableA which can be
compared with the "fifteenth record" in TableB?

 

[Tom Wickerath]

Hi Chris,

An easy way to compare this data within Access would be to use some software
marketed by FMS:

Total Access Detective
http://www.fmsinc.com/MicrosoftAccess/DatabaseCompare.html

If you export the data in each table to a text file, you can do the
comparison using a software product marketed by Scooter software. This method
will require that your export imparts the same order to the records:

Beyond Compare
http://www.scootersoftware.com/


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________