Solved Comparing Tables Help (1 Viewer)

[Cydee]

Hi Everyone,

I am using Access to create a data extraction form. This will contain two tables "Main" which will contain the data for all of the papers that I have reviewed, and then a second table "Validation" will be used for a second user to check over a subset of the entered records and independently extract the information.

The two tables have identical variables but "Validation" will contain a subset of the records that are in "Main" which will link on the variable ID.

I wanted to create a query that can compare each of the records in "Validation" with the corresponding record in "Main" and can in some way display any records/variables where the extracted data does not agree (i.e., if for the ID 204, "Main" had a sample size of 124 and "Validation" had a sample size of 142).

The way this information is not displayed is not that important, either a list of ID's that disagree or a table containing only records that disagree comes to mind?

So far I have used mostly query/form builders but I suspect SQL would be needed to do something like this so I'm a little out of my depth. All of the other advice I have found looks at comparing if a record shows up in both tables (Which would be unhelpful here as firstly, the majority of the records in "Main" will not show up in "Validation" and secondly the ID being in both tables does not confirm that the rest of the record agrees.)

Any help or advice would be greatly appreciated!

Best wishes,
Cydney

 

[Uncle Gizmo]

Have a look at the unmatched query that MS Access can build for you, with a wizard...

 

[Cydee]

Have a look at the unmatched query that MS Access can build for you, with a wizard...

Thank you for responding,

I did look at this but as far as I can tell (unless I'm wrong) this can only be built to compare on one criteria (I guess ID) so can only tell me if the record appears in both tables but not whether the record is the same in each table? Also since this is finding rows without an unrelated record will it not just tell me which records have not been validated rather than if the validation is agreeing with the main record.

E.g.

If I have Main table:
ID Size Country
001 124 USA
002 35 France
003 115 UK
004 192 USA

and Validation table:
ID Size Country
002 135 France
003 115 UK

I would want the query to give me that ID 002 needs to be rechecked (possibly more specifically that size is the issue but this is not vital).
Here, ID 003 is fine as they agree and IDs 001 and 004 are only in one table so do not need to be looked at.

Thanks,
Cydney

 

[Pat Hartman]

You would create a query that joins the two tables on the ID field. The Where clause would select only records where at least one pair of fields had a difference:

Where tbl1.fld1 & "" <> tbl2.fld1 & "" & "" OR tbl1.fld2 & "" <> tbl2.fld2 & """ OR ...

You can omit the concatenation part if you are positive that none of the fields contain null.

The form or report can use conditional formatting to highlight the pairs with differences

 

[Cydee]

You would create a query that joins the two tables on the ID field. The Where clause would select only records where at least one pair of fields had a difference:

Where tbl1.fld1 & "" <> tbl2.fld1 & "" OR tbl1.fld2 & "" <> tbl2.fld2 & """ OR ...

You can omit the concatenation part if you are positive that none of the fields contain null.

The form or report can use conditional formatting to highlight the pairs with differences

Hi Pat,

Thank you very much for your response!
I have just tested this out and it is doing exactly as I wanted for the first few variables!

Best wishes,
Cydney

 

[Pat Hartman]

You're welcome