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

mcornbill

Board Regular
Joined
Aug 24, 2005
Messages
79
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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
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

Board Regular
Joined
Aug 24, 2005
Messages
79
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. :biggrin:
 

NumbersMax

Board Regular
Joined
Jun 19, 2007
Messages
200
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
 

Forum statistics

Threads
1,181,373
Messages
5,929,571
Members
436,679
Latest member
helly123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top