Finding unmatched records for two identical queries

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
Hi All,

I have two identical queries from two Access databases, I need to check any unmatched fields every week, what will be the best solution to check that, please help me in detail If possible.

Both tables have more than 300k records

The other table will be added as a linked table in the current database.

* I need to find any unmatched fields so I can update it and keep both identical.

Please let me know if you have any question?

Thanks

ID= Unique


IDRoomFreezerShelfRackBoxPosition
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,971
you want an OUTER join query, to see whats missing from the list.

in a query, join your 2 tables,
dbl-click the join line,
chose SHOW ALL records in Tbl1, SOME in tbl2
OK
bring down the joined field in both tables.
run query

the items with NULL field are the missing ones.
you can filter ONLY missing ones with criteria: IS NULL
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
you want an OUTER join query, to see whats missing from the list.

in a query, join your 2 tables,
dbl-click the join line,
chose SHOW ALL records in Tbl1, SOME in tbl2
OK
bring down the joined field in both tables.
run query

the items with NULL field are the missing ones.
you can filter ONLY missing ones with criteria: IS NULL
Hi Thanks for your reply and help.

I was trying this in two test tables, Is this correct?

1607023122198.png
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
That is correct (left join). But beware this only means that there are unmatched IDs. It is not going to check if two records have the same ID but different values for (say) Freezer or Rack.

Also it is a one-way comparison: it will tell you if there are missing IDs in table2 that are in table1, but it does not tell you the reverse (... missing IDs in table1 that are in table2).
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91

ADVERTISEMENT

That is correct (left join). But beware this only means that there are unmatched IDs. It is not going to check if two records have the same ID but different values for (say) Freezer or Rack.

Also it is a one-way comparison: it will tell you if there are missing IDs in table2 that are in table1, but it does not tell you the reverse (... missing IDs in table1 that are in table2).
Hi Xenou,
I just want to make sure am not missing something here.

So the Field ID is Unique (primary key) cant be duplicated.
both tables have the same IDs (they match in both tables) but any other field might be different.

we have two databases old and new, few employees working on the old database and others working on the new DB and few working on both updating inventory , my task is to make sure that both inventories is identical, so I have to check entries every week to make sure both fields are the same.

any unmatched field in any database should be update to be like the other.

Does this query works fine or there is another way to do it?

thanks
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
Its a real pain what you are describing. The critical factor (to my mind anyway) is are there any nulls or could there be any nulls in any of these fields (assuming of course that the ID field cannot be null under any circumstances). If the answer is "No" then everything is much better... A second critical factor is if either table can have records (IDs) that are not in the other table.

Secondarily, if both sides are making changes simultaneously, then you had better make sure there is some way they are making sure they aren't changing the same records. If a record is "not the same" it can mean one side changed it, or the other side changed it, or both sides changed it - no way of knowing really in any of these cases (unless you have some copy of the original to refer to), and then its just random what change "wins".
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91

ADVERTISEMENT

Its a real pain what you are describing. The critical factor (to my mind anyway) is are there any nulls or could there be any nulls in any of these fields (assuming of course that the ID field cannot be null under any circumstances). If the answer is "No" then everything is much better... A second critical factor is if either table can have records (IDs) that are not in the other table.

Secondarily, if both sides are making changes simultaneously, then you had better make sure there is some way they are making sure they aren't changing the same records. If a record is "not the same" it can mean one side changed it, or the other side changed it, or both sides changed it - no way of knowing really in any of these cases (unless you have some copy of the original to refer to), and then its just random what change "wins".
ID feild is unique and cant be null.
Other fields can be anything (that can be blank)
To know which person entered first or another have changed it.
We asked the developers to add a feild showing the date (might also the time) to who made the changes last.

I will be only comparing the IDs that is available in the old database (this is why I linked both with the unique ID) any newer IDs created in the new one i don't have to update in the old one.

The new database is the major one and it contains all the IDs in the old one and the new ones)

I will be comparing both (only the ones in the old that match the ones in the new one) till we stop using the old one (few months).

So the result that I will get from the query (the unmatched rows) ill be checking the timestamp to know which one to update.
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
ID feild is unique and cant be null.
Other fields can be anything (that can be blank)
To know which person entered first or another have changed it.
We asked the developers to add a feild showing the date (might also the time) to who made the changes last.

I will be only comparing the IDs that is available in the old database (this is why I linked both with the unique ID) any newer IDs created in the new one i don't have to update in the old one.

The new database is the major one and it contains all the IDs in the old one and the new ones)

I will be comparing both (only the ones in the old that match the ones in the new one) till we stop using the old one (few months).

So the result that I will get from the query (the unmatched rows) ill be checking the timestamp to know which one to update.
Its a real pain what you are describing. The critical factor (to my mind anyway) is are there any nulls or could there be any nulls in any of these fields (assuming of course that the ID field cannot be null under any circumstances). If the answer is "No" then everything is much better... A second critical factor is if either table can have records (IDs) that are not in the other table.

Secondarily, if both sides are making changes simultaneously, then you had better make sure there is some way they are making sure they aren't changing the same records. If a record is "not the same" it can mean one side changed it, or the other side changed it, or both sides changed it - no way of knowing really in any of these cases (unless you have some copy of the original to refer to), and then its just random what change
you want an OUTER join query, to see whats missing from the list.

in a query, join your 2 tables,
dbl-click the join line,
chose SHOW ALL records in Tbl1, SOME in tbl2
OK
bring down the joined field in both tables.
run query

the items with NULL field are the missing ones.
you can filter ONLY missing ones with criteria: IS NULL
Oops I just noticed that this query will not work..this will onLy show the feilds that have null, but if there is difference in the feild it will not show..for example if all feilds are entered and table1 have a box bumber 5 and in table 2 have box number 2 it will not show
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
Xenou ,sorry I just noticed what you meant earlier, if both have same ID but differences in any other feild then it will not show it.

How can I get all results (blank or not)?
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
You need to explicitly test all the fields in the left join (not just join on ID - join on all the fields).

For example:

SQL:
select 
    t1.ID, 
    t1.Room as Room1,
    t1.Freezer as Freezer1, 
    t1.Shelf as Shelf1,
    t1.Rack as Rack1,
    t1.Box as Box1,
    t1.Position as Position1,
    t2.Room as Room2,
    t2.Freezer as Freezer2, 
    t2.Shelf as Shelf2,
    t2.Rack as Rack2,
    t2.Box as Box2,
    t2.Position as Position2
from #Table1 t1
left join #Table2 t2
on t1.ID = t2.ID
where
    (
    Nz(t1.Room, '---') <> Nz(t2.Room, '---')
    or Nz(t1.Freezer, '---') <> Nz(t2.Freezer, '---')
    or Nz(t1.Shelf, '---') <> Nz(t2.Shelf, '---')
    or Nz(t1.Rack, '---') <> Nz(t2.Rack, '---')
    or Nz(t1.Box, '---') <> Nz(t2.Box, '---')
    or Nz(t1.Position, '---') <> Nz(t2.Position, '---')
    )

It is important to remember that Null is always not equal to Null, which can be a false positive in a case where the first and the second table both have Null in the same field -- they would be considered inequal by the rules of equality, but to human thinking if both are Null then they are really the same.

Anyway, that's why the carefulness with Null values...

The rows with all Nulls on the table2 side represent records in table1 that are not in table2. The rest would be records in table1 that are in table2 but have different field values.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

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