Query and null join

[jaryszek]

Hi,

is here anyone who is usin method from this link?

https://www.tek-tips.com/viewthread.cfm?qid=843676

The method is for handling nulls in joins.

i do not why this is not working for me.

How this sql should look like with clause for empty rows?

UPDATE [A] 
INNER JOIN [B] ON ([A].Date = [B].Date) AND ([A].[Band] = [B].[Band]) AND ([A].Company = [B].Company) AND ([A].State = [B].State) AND ([A].[SAE Suffix] = [B].[SAE Suffix]) AND ([A].[SAE Root] = [B].[SAE Root]) 
SET [A].Qty = [B]!Qty;

Best,
Jacek

 

[jaryszek]

Ok,

we were discussing how to join empty fields here:

https://www.access-programmers.co.uk/forums/showthread.php?p=1605987#post1605987

so basically we have 2 methods:

When I want to do fuzzy/Null matching I build connecting keys. First you build a query for each individual datasource and create a calculated field that concatenates all fields that will be used for matching:

Table1ConnectionQuery
Field1, Field2, Key
2, 1, "~2~1~"
Null, 2, "~~2~"
Null, 3, "~~3~"

Table2ConnectionQuery
Field1, Field2, Key
2, 1, "~2~1~"
Null, 2, "~~2~"
Null, 3, "~~3~"

The Key field is simply this

Key: "~" & Field1 & "~" & Field2 & "~"

The special character helps to delimit the individual field values so that 1, 102 and 110, 2 don't accidentally match.

Then, I build another query using those 2 queries joining them via their Key fields. That way, since I want to match on Field1 and Field2 and Field1 is sometimes Null the match is still made.

and :

However, it may be safest to manage NULL values by first setting them to valid but unused values in each field of the import & destination fields 

For example, update null dates to 01/01/9999 and number fields to an unlikely value such as -1000000 
You will need to do this for each field separately in the two tables. For example: 

UPDATE tblData SET tblData.StartDate = #1/1/9999# WHERE (((tblData.StartDate) Is Null)); 
After completion run a further update query to revert the modified values in the destination table back to null. 

Once again do this for each field separately. For example: 

UPDATE tblData SET tblData.StartDate = Null WHERE (((tblData.StartDate)=#1/1/9999#));

First one (plog method) it seems more robust but what is the characters lenght limit in acces query?
What if your key will exceed this?

the second one (Colin method) seems to be ok but you have to know which columns contain nulls, what if whole table have nulls in different places?

Please help,
very important to me,
Best,
Jacek

 

[theDBguy]

Hi Jacek. I have used the concat method with success before. Why would you be concerned about the limit? Are you linking long text fields?

 

[The_Doc_Man]

Let's be clear here. TECHNICALLY if one of those fields in one of the JOIN ... ON elements is null, the records DO NOT MATCH. So as noted, you are working a "fuzzy math" comparison. You have another post here on the idea that you want to get rid of nulls. The issue is one that probably should be addressed BEFORE you get to the point of needing to do a fuzzy-math match-up involving the potential for null-match management. This is DEFINITELY a case where "divide and conquer" is the right approach and to my way of thinking (admittedly, sometimes a bit rigid where it probably shouldn't be), attacking and eliminating the nulls first is the better choice. Because the syntax is easier if you know for a fact that you won't have nulls.

 

[jaryszek]

thank you Guys very much!

I am closing topic,
Jacek

 

[theDBguy]

thank you Guys very much!

I am closing topic,
Jacek

Hi Jacek. Good luck!