Comparing differences between tables

[dmartin7]

I have two tables with the same structure, 25 fields and linked by date that should be identical. I want to create a query that compares the values within each field and checks for differences between the two tables. I've created an unmatched query which only compares one field at a time. I would like to compare all 25 fields at once and have the query produce the values that do not match within each field.

Thank you

 

[plog]

You have 25 fields that "should" be identical. Do you have 1 field that will be identical? What's the primary key field and is its value going to be the same in both tables for certain?

If so, you bring them both into a query object, INNER JOIN them by that field, bring down that id field into your query and run it. If the total number of records in that query match the total records in each table, then your tables match in terms of records. Next, you make 25 conditional statements that look like this:

Field1Different: Iif(Table1.[Field1] = Table2.[Field1], 0, 1)

That will identify what records have different values between the two tables. 1 caveat--I don't think it will work with NULLs. If any of those values might be NULL, you should create a custom function and test for them there.

 

[MarkK]

compare all 25 fields at once and have the query produce the values that do not match

I don't see how this can work if ALL the fields in the row might be different. A row represents a discrete thing; a unique instance of an object or entity. If there is nothing in the data that matches FOR SURE, then you might have two patients on the books named John Doe, and they might both make an appointment at your office at 2:30. If that happens, and there is no unique identifier in your data, then how do you tell your two John Does apart, and how do you tell their two appointments apart?

So given your description of the process, there is no way to make it work with certainty.

Sunrise looks exactly like sunset if you don't know the time of day, or the direction of view. Something has to be known for other things to be known.

hth
Mark

 

[jdraw]

dmartin7,

More info please. If you have 2 tables with identical field names, and you are looking for those records where field values are different, tell us more about the tables and records. A sample would also be helpful.