Comparing three (3) Tables in a MS Access database with CR11 Report

[0sprey]

I have one MDB file and three only Tables exist within the file. In each Table there is one identical main field.... {Part-Number}.
The Tables are imported from three different sources and they SHOULD contain identical data... however they are not identical... Table A is the Master and B & C should be identical with respect to {Part-Number}.

I wish to compare the Tables based on [Part-Number} and determine where un-match data fields exist in each of the three Tables... comparing only the data in {Part-Number} :

TABLE A TABLE B TABLE C
{Part-Number} {Part-Number} {Part-Number}

qwe234 qwe234 qwe234 < FULLY MATCHED!

dfe234 dfe234 < PARTIAL MATCH

xyz123 xzy123 < PARTIAL MATCH

erf456 < No MATCH

Can this be done in CR11 with one Report.... or are multiple individual reports needed ?

 

[Charliy]

Link the tables by part number using a left outer join.

then you need a few formulas:
@foundB
if isnull({tableB.partnumber}) then 0 else 1

@foundC
if isnull({tableC.partnumber}) then 0 else 1

@result
if {@foundB} + {@foundC} = 0 then "No Match"
else
if {@foundB} + {@foundC} = 1 then "Partial Match"
else
if {@foundB} + {@foundC} = 2 then "Full Match