Query to find unmatch data between two tables

[MTSPEER]

Hello,

I have two tables: PVE and TEST_EEAF.
PVE has around 220 fields and TEST_EEAF has around 140. They both have the same PK which is HICN. And they also both have some of the same fields on each table. I'm trying to write a query that will display all the rows with unmatched data between the two tables and show the field names. And if there are no unmatched rows show a message saying everything is matched. Thank you

[June7]

Can't have 'message' in the query. If everything matches, the query dataset will be empty.

[MTSPEER]

Okay. Can you help me with the first part then?

[orange]

Did you try the UNMATCHED query wizard?

[MTSPEER]

Yea but you can't do it to multiple fields in one query. It only lets you match one

[orange]

220 , even 140 fields in a table is extremely rare.
Tell us about the data, tell us about the file.
Why are you using 64 bit Office?

You can do, at least in theory, what you want by using 2 records sets.

Read Rs1 and rs2, compare field by field,
if rs1.fieldX <>rs2.fieldX then report the unmatch

Can you post the table structures? Or say 50 records from each table, and a description of how specifically the fields should match/not match?

Note: untested

Something like this might work

SELECT tbl220.Id
, tbl220.Field1
, tbl220.Field2,
....
tbl220.Field220
FROM 
tbl220 LEFT JOIN tbl140 
 ON tbl220.Key = tbl140.key  And
    tbl220.field1 = tbl140.field1  and
    tbl220.field2 = tbl140.field2
    ......
WHERE (((tbl220.somekeyfield) Is Null));

You could also try Untested
do a matching query (Inner Join query) and then do a query to find things in in tbl220 and not in the InnerJoin query.

Just some thoughts to consider. It would be better if readers knew about WHY you need this, and more about the tables involved.

[MTSPEER]

I can't post this database because it has private patient informarion. But I don't need all of the fields from each table to match each other. There is only going to be about 20 that I need to match up and find if there is unmatched data for. The WHERE clause in your code where it says "(((tbl220.somekeyfield)" is that PK from the beginning of the code "SELECT tbl220.Id" ?

[orange]

I was just suggesting some things as I dreamt them up.
Test a few things with some sample data. Make sure it works then add more until you get what you need.

[MTSPEER]

SELECT PVE.*
FROM PVE, TEST_EEAF
WHERE PVE.HICN LIKE "%" & TEST_EEAF.HICN & "%";

Won't this code compare the field name HICN from both of the tables and if there is an unmatched record display that record from the PVE table?

[orange]

No, you don't have any Join defined.
You get a cartesian product and your Where clause doesn't make sense.

This would be basic to find matches:

SELECT PVE.*
FROM PVE, TEST_EEAF
WHERE PVE.HICN = TEST_EEAF.HICN;

OR

SELECT PVE.*
FROM PVE INNER JOIN TEST_EEAF
ON PVE.HICN = TEST_EEAF.HICN;

[MTSPEER]

Okay, But I am trying to a solution that lets me check multiple different fields like about 20. I don't want to have to write that over and over again to compare each field