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

 

[dankar]

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

 

[xenou]

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).

 

[dankar]

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]

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]

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]

I think the best way to check for nulls is to use sql directly:

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.