Finding unmatched records for two identical queries

[dankar]

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

ID

Room

Freezer

Shelf

Rack

Box

Position

 

[ranman256]

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]

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?

 

[xenou]

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]

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]

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]

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]

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]

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]

You need to explicitly test all the fields in the left join (not just join on ID - join on all the fields).

For example:

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.