What Field is Not Matching in Unmatched Query

[Ronster]

I'm running the Unmatched Query on two tables each having the following
fields:

FIELD1 SSN
FIELD2 LAST NAME
FIELD3 FIRST NAME
FIELD4 CUSOMER_ID
FIELD5 (empty)

I've linked all 4 fields from table 1 to table 2 and changed the Join
Properties to option 2.
I've set the SSN field criteria in Table 2 to IS Null. It looks to me
like if any 4 of these fields do not match Table 1 it will display in
the query table. The problem is I don't know which field is not
matching. Is there a way to display in the EMPTY field which field is
not matching? For example if the SSN does not match it displays "SSN"
or if the SSN does match but the Last Name is not the same it displays
"Last Name." I'm trying to generate an error report to help people fix
the records that do not match. Any ideas?

 

[Smartin]

I'm running the Unmatched Query on two tables each having the following
fields:

FIELD1 SSN
FIELD2 LAST NAME
FIELD3 FIRST NAME
FIELD4 CUSOMER_ID
FIELD5 (empty)

I've linked all 4 fields from table 1 to table 2 and changed the Join
Properties to option 2.
I've set the SSN field criteria in Table 2 to IS Null. It looks to me
like if any 4 of these fields do not match Table 1 it will display in
the query table. The problem is I don't know which field is not
matching. Is there a way to display in the EMPTY field which field is
not matching? For example if the SSN does not match it displays "SSN"
or if the SSN does match but the Last Name is not the same it displays
"Last Name." I'm trying to generate an error report to help people fix
the records that do not match. Any ideas?

Maybe you need four queries? If I understand you correctly you want to
see table B where any three, but not all four of the corresponding
fields in table A match? You can use UNION to put the four queries together.

BTB... Null may not be your friend either.

SELECT
A.FIELD1
, A.FIELD2
, A.FIELD3
, A.FIELD4
FROM ZETABLE AS A INNER JOIN ZETABLE AS B
ON A.FIELD2=B.FIELD2
AND A.FIELD3=B.FIELD3
AND A.FIELD4=B.FIELD4
WHERE A.FIELD1 <> B.FIELD1

UNION

SELECT
A.FIELD1
, A.FIELD2
, A.FIELD3
, A.FIELD4
FROM ZETABLE AS A INNER JOIN ZETABLE AS B
ON A.FIELD3=B.FIELD3
AND A.FIELD4=B.FIELD4
AND A.FIELD1=B.FIELD2
WHERE A.FIELD2 <> B.FIELD2

UNION

etc...