Can I do this with a query?

[Kokomoman]

I'm really new to writing queries so I was hoping someone might be able to steer me in the right direction. I'm using Access 2000 and I want to find a way to compare 2 tables and find any discrepancies between them. To help explain this I've attached a file to hopefully illustrate what I'm trying to do.

I really appreciate anyone's help on this. Thank you

 

[dcx693]

This is possible to do, but not with a query alone. I came up with a solution, but it's not elegant.

Using ADO or DAO and VBA, I would first create a recordset consisting of the two tables joined by the primary key. I would loop through all the records in turn, sort the fields 2-5 in some order so that I could then do some type of comparison between the fields, say table1 field 2 to table2 field 2.

You actually wouldn't need to sort them, you could compare table 1 field 2 to table2 field2, then table 2 field 3, etc.... until you found a match then continue on, but I think the sorting will make things move faster. It will make the programming a bit more complex, so it's optional if speed is not an issue.

Now, what to do if a discrepancy is found. You could write those to another table, or just print those out to the debug window depending on what you eventually need to do with them.

Would there ever be a case where a record existed on table 1 but not table 2? That should not be a problem depending on how you set up the program. When a comparison is made between table 1 field 2 and table 2 field 2 and a corresponding value is not found, it will just be another discrepancy.

 

[Kokomoman]

Thanks for your time in looking at my problem. I was hoping to avoid writing code to do that but I understand where your coming from. Thanks again.

 

[Nouba]

I guess the data structure is not right when camparing values over more than one field. Use the search button here in the forum or google for data normalisation.

 

[jfgambit]

You can create a query, add both tables and join on the primary key. I would add the corresponding columns from each table next to each other (i.e., Last Name from table 1, Last Name from table 2), then create a calculated column something like this (for a text value) (and this will NOT flag capitalization differences):

IsDataErrorLastName: IIf(Trim([tblName1.LastName]) = Trim(tblName2.LastName),0,1)

Do this for all the fields of interest. Then create a final calculated column that sums up all the IsDataError... calculated fields. Return only those records (set a criteria) where the sum of the calculated fields is <> 0. This will display only the records where there are data differences.

From another post....

Give this a try

 

[Kokomoman]

Thanks for your replies. I'll try your suggestions and hope for the best.