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
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
Sure, it makes sense to use inner join as long as you aren't looking for unmatched records (i.e. an ID that is in one table that is not in the other). If you are always comparing ids that exist in both tables then inner join is best (and faster).

The idea with "---" ...

consider these two made up rows:

1 Cat Dog Null Rat
1 Cat Dog Null Rat

These two rows would be considered "not equal" by usual SQL rules, because 1 = 1, Cat = Cat, Dog = Dog, Rat = Rat, but ... Null <> Null !!
Usually for comparing data we would want to consider them as equal.
So my query in fact would treat the two rows as:

1 Cat Dog --- Rat
1 Cat Dog --- Rat

Now the two nulls are compared both as being three dashes (an arbitrary string value that means nothing in itself) and they can be evaluated as equal for that reason. That's the idea, anyway. I'm not sure I'm understanding what I'm seeing in the above picture (what is Expr1015, Expr1016, etc.) ... basically if Room, Freezer, Shelf, Rack, Box, and Position are null on both sides then they should come out as "equal" in this query (therefore, not included in results if all fields are "equal").

For the benefit of wider discussion (but irrelevant otherwise) I'll mention that in most database systems you could just use EXCEPT and usually Nulls are handled correctly too:
one way --> select * from Table1 EXCEPT select * from Table2
both ways --> select * from Table1 EXCEPT select * from Table2 UNION select * from Table2 EXCEPT select * from Table1
Thank you for the explanation,
as for Expr1015,... its when I'm entering the formula , access giving it a name (Highlighted in yellow) such as Expr1,Expr2...

one thing I just tried and it worked and excluded the ones that have the null values that I removed the "---" and made it "" , I don't understand why though as what in between the "" should not matter, with the "" (showing below it should matching blank to blank.

it worked but does it make since like this and is it ok to keep it like this?

thanks

1607549891967.png
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
Yes that's fine, its true shouldn't matter what's between the "".

I think actually you can uncheck that checkbox that you see in the last image ... you don't need to "show" those "fields" since they are really there in the design grid for adding the formulas...

Well, one caveat about what goes between the "" is that you generally want to have something that isn't actually a likely datapoint. For instance, it is possible in some datasets that there would be zero-length strings. So these might not be good if treated as the same as Null. I generally choose an arbitrary but unlikely character (maybe something like ^ or ^# or even =^..^= (but zero length strings will do in a pinch).
 
Last edited:

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
Thank you very much xenou, yes I have them unchecked, I was only showing the whole fields for you to see if there is anything wrong.

I tried many different characters and also the one you mentioned its still not working with the null values.

the only one that works either "" or " " (if I put space), I still don't know why, but the "" working fine with me.

thanks again for you help
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
No problem. As a supposition on the issue with Nz(field, '') vs Nz(field, '---') ... maybe you actually do have Nulls on one side and zero-length strings on the other, and for your purposes these are best treated as "the same".
 

dankar

Board Regular
Joined
Mar 23, 2016
Messages
91
No problem. As a supposition on the issue with Nz(field, '') vs Nz(field, '---') ... maybe you actually do have Nulls on one side and zero-length strings on the other, and for your purposes these are best treated as "the same".
I thought about that,but I'm not sure if sid right.

So I copied the rows with blank into excell and I used the Len() to check, is this right (they were all equal)
If not how can I check the zero-length form the Nulls.

Thanks
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
I think the best way to check for nulls is to use sql directly:
SQL:
Select * From Table Where Field Is Null

However, there is also an IsNull() function in MSAccess, which presumably does the same thing ( but it is usually used in combination with IIF() )

If you check string length only, then I believe that null and zero length strings will have a length of 0. So going by string length alone would not be enough.

I.e.,

Nulls:
  • Length is zero
  • Is Null
  • not equal to ''
  • Not equal to Null (!)

Empty Strings/Blanks:
  • Length is zero
  • Is Not Null
  • Equal to ''
  • Not equal to Null

Unfortunately (or not) MSAccess displays nulls and zero length strings as blanks. You cannot tell the difference by looking at the data.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,840
Messages
5,627,186
Members
416,228
Latest member
JOOTEISHERE

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