Hi,

I would like to have some advise on how to highlight missing data elements when comparing a table against a reference table by using a query in access.


The case is as follows:



There is a reference table consisting of 3 columns of data e.g. DATA1, DATA2 and DATA3.

There is also a second table also consisting of 3 columns of data DATA1, DATA2 and DATA3.

I want to compare the second table against the reference table on all 3 columns DATA1, DATA2, DATA3 and I want to highlight any missing/incorrect data elements in the second table.

All the records/rows that have identical values on ALL 3 columns DATA1, DATA2, DATA3 are considered a perfect match and are OK.

For all the remaining records in the second table that do no have identical values on ALL 3 columns of DATA1, DATA2, DATA3 I would still like to have them matched against the equivalent records in the reference table (if possible), but with the incorrect/missing data elements in the second table highlighted. I will try to explain below with some examples.

PERFECT MATCH
Reference table: DATA1 = 5 , DATA2 = 23 , DATA3 = XYZ
Second table: DATA1 = 5 , DATA2 = 23, DATA3 = XYZ

MATCH WITH MISSING DATA
Reference table: DATA1 = 221, DATA2 = 34, DATA3 = QPR
Second table: DATA1 = 221, DATA2 = 34, DATA3 =

As you can see there is no value for DATA3 in the second table. I still want
access to match these two records, but I would like to have the cell with the missing value highlighted in red in my query result.

MATCH WITH INCORRECT DATA
Reference table: DATA1 = 123 , DATA2 = 56 , DATA3 = AAR
Second table: DATA1 = 123, DATA2 = 56, DATA3 = TTP

As you can see there is an incorrect value for DATA3 in the second table. I still want access to match these two records, but I would like to have the cell with the incorrect value highlighted in red in my query result. (it's also possible that there is only a match on for example DATA2 and that both DATA1, DATA3 are missing/incorrect...makes things more complicated I guess).

Hope it's clear and hope that anybody would be able to advise?
I know the basics of running query's in access, but I can not figure out how to achieve the above mentioned. I already ran a query using joins for DATA1, DATA2, DATA3 and this query provides matching records on the three columns. I do not really know how I can systematically compare the remaining records an highlight the incorrect/missing data elements.

Thanks,
Flo