Do null values adversely affect join properties in Access 2010?

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.
Liberty4allAsked:
Who is Participating?
 
PatHartmanConnect With a Mentor Commented:
If Committee name is optional, it does not belong as part of the primary key.  You should never be attempting to join on an optional field.

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.  It is very confusing to allow both and complicates the SQL unnecessarily.  I choose to allow Null rather than ZLS because that means that numeric and date fields don't work differently from text fields.  ZLS (STRING!!!!) is obviously not valid for a number or date field but null is allowed for all datatypes provided the field is not defined as required at the table leve.

If you have an optional field that you are using as part of the primary key, you should redesign the table since the optional data in this case should be in a separate table.  Once the optional data is in a separate table, you would use a Left Join rather than an Inner Join.  That will allow you to join to a specific value when it is there  and still retrieve the records from the first table even when the optional table has no data.

As I said earlier, if you don't want to take my advice on normalization, then you have no choice but to allow ZLS in the committee field AND you need to make the field REQUIRED at the table level and make its default ZLS which is entered as "".   In your form, you should also ensure that the committee field is not null because you don't want the user to get an error message.  You can simply substitute Null for ZLS.

The suggestion by Pawan was to make the "empty" values null.  All that does is complicate the join.  It is possible to validly join ZLS to ZLS because as I already mentioned, ZLS is something as far as SQL is concerned whereas it is NOT possible to validly join null to null since null is nothing and nothing doesn't equal anything even itself.  So any comparison that involves a null value can never return true, therefore null can never join to null.  It does actually make sense once you get a grip on the difference between the two values.  Conceptually ZLS is something whereas Null is nothing.
1
 
Pawan KumarDatabase ExpertCommented:
Pls try this -

UPDATE tblBoardCommitteeImport1F
SET tblBoardCommitteeImport1F.CommitteeName = NULL
WHERE LENGTH(tblBoardCommitteeImport1F.CommitteeName) = 0
0
 
Pawan KumarDatabase ExpertCommented:
Edited my last comment.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
Liberty4allAuthor 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?
0
 
PatHartmanCommented:
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.
0
 
Ryan ChongCommented:
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

0
 
PatHartmanCommented:
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.
0
 
PatHartmanCommented:
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.
0
 
Ryan ChongCommented:
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.
0
 
Liberty4allAuthor Commented:
Ryan - please tell me how to modify my query syntax to incorporate your suggestion:
AND ( nz(Table1.field,'') = nz(Table2.field,''))
0
 
Pawan KumarDatabase ExpertCommented:
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

0
 
Ryan ChongCommented:
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.
0
 
Liberty4allAuthor 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.
0
 
Ryan ChongCommented:
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?
0
 
Pawan KumarDatabase ExpertCommented:
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.
0
 
PatHartmanCommented:
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.
0
 
Liberty4allAuthor 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.
0
 
Liberty4allAuthor 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.
0
 
Liberty4allAuthor 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
0
 
PatHartmanCommented:
Been there and have the scars to prove it.  You're welcome :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.