Difficulty Comparing Two Tables

[rjbailey87]

Hey All,

I'm a beginner access user and have been unable to find the answer to this question.

I need to figure out the percentage difference between two identically structured access tables. Essentially, we had two people enter survey data (one person entered the originals and the second person entered 10% of the originals to QC the work of the first person).

I already have a query that identifies every record that contains a difference, but I am unable to figure out how to automatically identify the values that are different between tables. There are about 60 fields, so manually identifying the values with differences will lead to errors.

I am hoping to end up with an output that either counts the total differences or builds a table like this (x signifies an error)...

Field 1, Field 2, Field 3,,,,,,
ID x



Thanks for any help. And if you think this issue would be resolved faster outside of access, suggestions for other programs would be great!

 

[Guus2005]

please provide a sample database containing sample data representing you problem.
you mention 60 fields. since the problem is the same for three fields, please limit the size of the data.

HTH

 

[plog]

Do the two tables share a unique identifier? If so, could that unique identifier be incorrect between tables? Essentially I am wondering how you know which records correspond to each other between tables?

Also, how are you going to calculate accuracy, is it per field or per record? You say you have around 60 fields in a record, do you want the accuracy based on fields or records? For example, suppose you have 10 records which translates into 600 fields. Now suppose 3 records have errors, of which 1 record has 4 fields that are different. What is your accuracy? 70% (7 out of 10 records) or 99% (594 out of 600 fields)

 

[Privateer]

RJBailey87,
You can do simple math in a query and give the formula an alias "Problem" followed by a colon. Try this:

Problem: IIf([tblCompensation].[Compensation]=[tblCompQC].[Compensation],"OK","Error")

I tested this on a simple compensation table and changed some values so they would be different. Whether the values are numbers or text you can simply ask if they are equal and put a text phrase or a number in the true false areas.
Privateer