Comparing tables for non matches

O

oldrog

I started out with two tables imported from a peoplesoft database. The tables
contain four fields that need to be checked for matches. I created a
make-table query that found the matches by putting in statement that read
like: match field 1 and match field 2 and match field 3 and match field 4.
This worked great! Then I needed to get a list of the non-matches. This
proved to be difficult. First I tried to create another make-table query that
did: if table 1.field 1 <> table 2.field 1 OR table 1.field 2 <> table
2.field2 OR table1.field3 <> table2.field3 OR table1.field4 <> table2.field4.
That overloaded my virtual memory. I tied a few other queries that either
gave bad results or the same Temp memory error. What is needed is to look at
a single row of info in table 1 and then look at each row in table 2 and see
if all four fields match. Then some way of indicating if the row matches or
not. It would then need to get the next row from table1 and compare with all
the rows in table 2 again, so that I would end up knowing if every row in
table 2 is a match or not.
 
K

Ken Snell \(MVP\)

Try a query similar to this to show you the duplicates in Table2 for a
record in Table1:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1 AND
Table1.Field2 = Table2.Field2 AND
Table1.Field3 = Table2.Field3 AND
Table1.Field4 = Table2.Field4;
 
B

Bob Miller

Try using the Find Unmatched Query Wizard.
I write the following to help you and others in the future:
Why do things for yourself when Access will do them for you? I see a
lot of people in this group, beginners and experts, who want to code
this or that or create a query using SQL when Access will do all the
work for you and do it faster. The end product is the same.
Had you done this from the beginning you would have a finished product
in about 5 minutes and then had a cup of coffee with the spare time you
created for yourself.
Don't get me wrong. Code is necessary as the db gets more
sophisticated. I use it a lot for forms and reports. It's just that
we need to simplify when possible.
 
K

Ken Snell \(MVP\)

Just add a criterion to find the nonmatches:

SELECT Table1.*, Table2.*
FROM Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1 AND
Table1.Field2 = Table2.Field2 AND
Table1.Field3 = Table2.Field3 AND
Table1.Field4 = Table2.Field4
WHERE Table2.Field1 Is Null;
 
O

oldrog

This was very helpful. I actually used an OUTER JOIN to get all the records
from table 2 and then used IS NULL in the table1 fields. The only problem I
have now is that for some unknown reason I am getting 23 more records (out of
149,228) then what was in the original table 2 to start with!?
 
K

Ken Snell \(MVP\)

My error... the INNER JOIN should have been replaced by a LEFT JOIN, for the
example that I posted.

Not having any idea of the data that are in the tables, it's hard to give a
reasonable suggestion for the "duplication" of records (and I don't know if
the duplication is from the INNER JOIN query or from the outer join query?).
 
Top