We help IT Professionals succeed at work.

Do null values adversely affect join properties in Access 2010?

204 Views
Last Modified: 2017-11-05
I have a query that joins two tables using three fields in Access 2010.  The fields used in the inner joins have identical data types.  When joining to only two of the three fields the query returns five records but when adding the third join nothing is returned.  Of the five records returned, only one is populated for the field CommitteeName in one table and the rest are null.  I can understand why that record is not returned when adding a third join but don't understand why the other four should be excluded.  The data type of the field in both tables is text and Allow Zero Length = Yes.  

I ran the following query to replace all blanks with null in both tables thinking this would solve it but no change in results occurred.
UPDATE tblBoardCommitteeImport1F SET tblBoardCommitteeImport1F.CommitteeName = Null
WHERE (((Nz([tblBoardCommitteeImport1F].[CommitteeName],''))=''));

I'm guessing the problem has to do with null values in the CommitteeName field in one or both tables but am not sure what to try next.  Any advice on how to troubleshoot this further would be appreciated.
Comment
Watch Question

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Pls try this -

UPDATE tblBoardCommitteeImport1F
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LENGTH(tblBoardCommitteeImport1F.CommitteeName) = 0
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Edited my last comment.
Liberty4allRetired

Author

Commented:
I get "Undefined function 'Length' in expression" when trying the above.  When I change LENGTH to LEN no records are updated.  What should I try next?
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Null and ZLS are different values.  ZLS is represented as "" and Null is represented as Null.  If you want to determine if a field is Null, you MUST use the IsNull() function in VBA and in SQL you would use "Is Null"  Or the IsNull() function.

Null never equals null so examine the following expressions.  Both will return false when a and b are both null and also when EITHER a or b is false.
If a = b then
    msgbox "True"
Else
    msgbox "False"
End If
If a <> b then
    msgbox "True"
Else
    msgbox "False"
End If

If you need to join on a column that might be empty, then you will have to set allow ZLS to Yes, set the default to "" and in your Form's BeforeUpdate event make sure that null is replaced with ZLS if necessary.  So your update query did exactly the opposite of what it needed to do.  It set ZLS to nulls rather than setting Null to ZLS.

But, the reality is that "key" fields should NEVER be null.  That means you actually have a design flaw and need to create additional tables.  Hopefully only one of the three columns can legitimately be null and that is the "lowest" in the hierarchy.  Split the data associated with that third field into a separate table and since it is optional, always use a Left join in your queries.
CERTIFIED EXPERT

Commented:
I'm guessing the problem has to do with null values in the CommitteeName field in one or both tables but am not sure what to try next.
exactly, or you may consider to convert the null in your joining condition, which you can do that by applying NZ function, like:

 AND ( nz(Table1.field,'') = nz(Table2.field,'')) 

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
There is a typo in my previous comment but I couldn't fix it because Ryan had already posted.

Null never equals null so examine the following expressions.  Both will return false when a and b are both null and also when EITHER a or b is false.
should be

Null never equals null so examine the following expressions.  Both will return false when a and b are both null and also when EITHER a or b is Null.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Do NOT convert fields on the fly in the join clause of the query.  This will make the join not sargeable.  Fix the data (and KEEP it fixed as I suggested by making the field required)  if you decide not to take my advice to fix the underlying problem.
CERTIFIED EXPERT

Commented:
When joining to only two of the three fields the query returns five records but when adding the third join nothing is returned.
it also depends on your joining conditions and what data to be extracted from your Select statement. You may consider to use different types of Joining clause, like Left join to examine your SQL results.
Liberty4allRetired

Author

Commented:
Ryan - please tell me how to modify my query syntax to incorporate your suggestion:
AND ( nz(Table1.field,'') = nz(Table2.field,''))
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
This way is should be fine.

AND Table1.field = Table2.field  AND ( Table1.field IS NULL OR Table2.field IS NULL )

Open in new window

CERTIFIED EXPERT

Commented:
please tell me how to modify my query syntax to incorporate your suggestion:
in general, if you're familiar with this.. you can edit your Query by go to SQL view and then adding the additional condition(s) there.

Or you may post your Select SQL and we will try to give you suggestions then.
Liberty4allRetired

Author

Commented:
I started with this:
UPDATE tblBoardCommitteeImport1F
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LENGTH(tblBoardCommitteeImport1F.CommitteeName) = 0

I'm content to update one table at a time.  I don't know how to add your suggestion: AND Table1.field = Table2.field  AND ( Table1.field IS NULL OR Table2.field IS NULL ) to the above since my syntax refers to only one table and yours to two.  If I need to use both tables in the query I can do that but please give me the complete syntax.
CERTIFIED EXPERT

Commented:
ok, so you have updated your field: CommitteeName in table: tblBoardCommitteeImport1F to null if it's zero length.

so, will it help when joining  the 3rd condition?
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
After running below
UPDATE tblBoardCommitteeImport1F 
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LEN(tblBoardCommitteeImport1F.CommitteeName) = 0

Open in new window

Did u get the NULL values in your table.
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Liberty4all,
Are you trying to join null to null OR null to everything?

If you reread my original post, I tell you that you cannot join null to null and you can see by the path the others are taking you down that I am correct.  So if you really want to join "null" to "null", then convert null to ZLS - ON WHICH YOU CAN JOIN without any problem because ZLS is something whereas Null is nothing.  You just have to make sure to never allow nulls to creep back in.  

If you want to join null to everything,  you will need to do it with criteria and you will almost certainly end up with a Cartesian Product so test carefully.

The best solution always comes down to fixing the design problem which in this case dictates an additional table for the "optional" data.
Liberty4allRetired

Author

Commented:
Pat,

I appreciate your additional comments.  I've never encountered data like this until recently so I'm totally perplexed by what is happening.  Your explanation on why null is different than a zero length string (ZLS) is helpful.  Let me explain more of what I'm doing to add context to our discussion.

I'm importing data from Excel that is produced automatically by a system over which I have no control.  After importing this data is compared to an existing table by joining on three fields.  The field that is causing the problem is named CommitteeName.  Both the imported data and existing (main) table have the same properties for the CommitteeName field: data type is text and Allow Zero Length = Yes.  On the surface this appears to be fine but I then discover the following.

After importing I find all 501 records when WHERE is set to IS NOT NULL for the CommitteeName field but the majority of these are blank.  When WHERE is set to IS NULL no records are returned.  This is not what I was expecting given my lack of experience with ZLS.  Using your suggestion I then set WHERE = "" and get 359 records which are blank and 142 that are populated with WHERE = NOT "".  359 + 142 = 501.

I need to import all records regardless of whether the CommitteeName field is populated.  Often times it will not be but that is expected.  I can learn to use "" instead of Null when checking for blank values.  What is most important, however, is my inner joins work as expected.  Do I need to use WHERE NOT "" or something else in the query that checks for records that match in both tables?  A slimmed down version of this query is shown below.

SELECT tblBoardCommitteeImport1F.[Donor ID], tblBoardCommitteeImport1F.CommitteeName, tblBoardCommitteeImport1F.[Legal Name] INTO tblDupBoardCommittee1
FROM tblBoardCommitteeImport1F INNER JOIN [main table] ON (tblBoardCommitteeImport1F.[Donor ID] = [main table].EmpID) AND (tblBoardCommitteeImport1F.[Legal Name] = [main table].[Org or Project Name]) AND (tblBoardCommitteeImport1F.[CommitteeName] = [main table].CommitteeName)
ORDER BY tblBoardCommitteeImport1F.[Donor ID];

Please advise what changes you recommend in the above query or anything else to insure that I'm getting the desired result.  Thanks.
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Liberty4allRetired

Author

Commented:
Your two sentences shown below are key to fixing my problem.
"In my databases I NEVER allow ZLS except if I have to import into a temp table from an external source.  When I append from the temp table to the actual tables, I convert any ZLS to null."  I'm modifying my process to match your advice because I agree with it.  I'm still encountering things that don't make sense but am finding ways to work around them.  When finished I will close the question but am keeping it open until then in case something I can't fix pops up.
Liberty4allRetired

Author

Commented:
Pat,

Thanks for taking the extra time to respond to my question as that provided the solution I needed.  As noted above your comments about never allowing ZLS values except when importing to a temp table is identical to my situation.  Now that I know how to identify ZLS values using double quotes ("") and understand why IS NOT NULL can include blank records due to ZLS values your advice is tailored exactly to my needs.  Thanks for saying it very clearly.  

Bryan
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Been there and have the scars to prove it.  You're welcome :)

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions