Null values in query

[ScottH]

I have a database where I regularly import refreshed tables. The tables
show employees whose records have been rejected in a sync run between
SAP and a mainframe.

Some of these employee records are not worth fixing... ie, employees
who have left the company. As a result, I am creating an "exclusion"
table that will contain the records that should be ignored.

When the fresh rejection tables are loaded, I need to query the fresh
data and remove the rows that exactly match a row in the exclusion
table.

I need some help with the query, however, because it is having troubles
when I try to match null values. I've started with a select query,
trying to get the format right before moving on to any deleting. Here
is the SQL I'm currently using:

SELECT DISTINCT tblAllDataMerged.*
FROM tblExcludedRecords
INNER JOIN tblAllDataMerged
ON (tblExcludedRecords.[GEMS Lastname] = tblAllDataMerged.[GEMS
Lastname]) AND (tblExcludedRecords.SSN = tblAllDataMerged.SSN);

SSN (Social Security Number) is always populated, but Lastname is
frequently null. The query omits all of the rows that have a null value
in the joined fields.

Can someone help me understand what I need to do to get this to work?

Thanks!

Scott

 

[Jeff Boyce]

Scott

I don't think I understand. If you have potentially Null values for [GEMS
Lastname], how can you hope to "match". A Null compared to a Null would
return a False, since there's no way to see if two unknowable values are the
same (?!they're unknowable!).

Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You could change the join condition by forcing nulls to be a zero length string.

SELECT DISTINCT tblAllDataMerged.*
FROM tblExcludedRecords
INNER JOIN tblAllDataMerged
ON (tblExcludedRecords.[GEMS Lastname] & "" =
tblAllDataMerged.[GEMS Lastname]) & ""
AND (tblExcludedRecords.SSN = tblAllDataMerged.SSN);

You could also just join on the SSN and then use a where clause to match last
name on the values being the same or both last name fields being null. This
might be faster than using the join above since it could use any index on the
lastname fields.

SELECT DISTINCT tblAllDataMerged.*
FROM tblExcludedRecords
INNER JOIN tblAllDataMerged
ON (tblExcludedRecords.[GEMS Lastname] =
tblAllDataMerged.[GEMS Lastname])
AND (tblExcludedRecords.SSN = tblAllDataMerged.SSN)
WHERE (tblExcludedRecords.[GEMS Lastname] =
tblAllDataMerged.[GEMS Lastname]) or
(tblExcludedRecords.[GEMS Lastname] IS NULL
AND tblAllDataMerged.[GEMS Lastname] Is NULL)

 

[Guest]

Thanks, John... that's an excellent suggestion. The first option seems
like it might work best for my situation.

Thanks again!

Scott