Finding new records that are in one table but not the other

[mcornbill]

Hi all

This is driving me spare. I have been trying for hours, using all of the info in help and off the internet, to find records that are in one table but not the other, using the unmatched record query in Access.

I have two file both in exactly the same format like this:

Vehicle Record No.
Service Date
Invoice Number
Service Code
Service Details
Mileage
Value
event type id

All I want to do is compare the two file's records on every single field. I.e. if there is a record that is the same in both files, on every single field but just one of them differs, I want it to bring it across into a separate table. If the record matches on EVERY field then I'm not interested in it. Only the one file is going to have any of the records I'm interested in.

Any help, save me from insanity please!

Cheers
Mark

 

[NumbersMax]

Mark,

If I understand correctly: "Only the one file is going to have any of the records I'm interested in. "


1) Link all fields from the table (table1) that will have the records you are interested in to the other table (table2).
2) Adjust the link so that it indicates all records from table1 and those records that match from table2.
3) Double-click each field in table1 to add to the query design
4) Double-click each field in table2 to add to query design
5) Uncheck the "Show" check box for the fields from table2
6) Enter Is Null in the criteria rows for each of the fields from table2 on separate criteria rows so it appears to be going diagonally down the criteria rows.

When you read the criteria, the logic should play out like this:

Give me all the data from table1 (those fields that are still checked to show up on the query) where:
table2.field1 Is Null OR
table2.field2 Is Null OR
table2.field3 Is Null ...

Separate criteria rows mean OR. If you put criteria on the same row it means AND.

I believe this will work! Let us know how it goes...
Max

 

[mcornbill]

Thanks a lot mate that has worked a treat!

It was the whole "Is Null" thing that I don't understand. I can understand the AND or OR logic now but doesn't "Is Null" mean "Is Blank" basically? If so why are we checking to see if those fields are blank in the query?

Thanks again.

 

[NumbersMax]

Glad I could help!

The relationship links are the key and it's a little hard for me to explain but I'll try.

When you link in a way that tells Access to give you all records from table1 and only those that match from table2, then the values in the fields for table2 either match table1 or they are Null.

I believe that is what allows you to set criteria to Is Null to get the unmatched stuff.

If you do trial an unmatched record query using the wizard, you will see that it automatically sets an Is Null in the criteria based on the information you give it.

Max