Having problems with Nulls in joins

[RJLLodigiani]

I have Table PERSON

NAME LASTNAME
John Doe
{{null}} Smith

I have another table PERSON1 (is copy of table PERSON)

NAME LASTNAME
John Doe
{{null}} Smith

I write query with this joins -----



PERSON PERSON1
NAME -------- NAME
LASTNAME ---- LASTNAME

It only returns John Doe (ignores Smith).

I would like to return John Doe and Smith).

I have to do this because in one table I have the primary key and in the copy I don't have the same primary key because I deleted by mistake.

[ranman256]

You cant match nulls. They dont exist, so they cant match.

You'd need a special query with [name] is null , but match [lastname]

[JoeM]

You can use the NZ function to handle those NULL values in your joins.
You can edit the SQL code of your Query directly to look something like this:

SELECT Person.NAME, Person.LASTNAME
FROM Person 
INNER JOIN Person1 
ON (Person.LASTNAME = Person1.LASTNAME) 
AND (NZ(Person.NAME,"X") = NZ(Person1.NAME,"X"));

Just note that it may have unintended consequences, such as if you have multiple "Smith" entries missing the first name, they will all be linked to each other (which could create One-to-Many and Many-to-Many joins).

[RJLLodigiani]

The use of NZ() --I did not use the "X", seems to be working. It is possible I have encountered you warning because I have three more records than the original. But this is good because it makes me do a more thorough cleaning of my data. Thank you!

[JoeM]

The use of NZ() --I did not use the "X", seems to be working.

You just need to use some non-null/blank value, and have it equal for both NZs to get it to work.

It is possible I have encountered you warning because I have three more records than the original. But this is good because it makes me do a more thorough cleaning of my data.

Yes, your linking fields need to create/link unique records, or else you will have problems.

Thank you!

Your welcome!

[RJLLodigiani]

I have another for you.

I have the table PERSON
ID
Name
Name2
Lastname
Lastname2

ID is the PK, Lastname is Required, the rest of the fields are optional.
I created an index FPK UNIQUE on Lastname,Lastname2, Name,Name2.

However, Access will not prevent me from entering [Lastname,null,null,null] twice.

Shall I work with EmptyString instead of Nulls?

[JoeM]

I don't think it is a good idea to force Unique Indexes on fields that are not required.

[RJLLodigiani]

I think I got it.

In my mind, and although a NULL "is not data", I was considering "no data" as a piece of knowledge. Being NULL different than blank.

JOHN NULL NULL
JOHN blank blank
JOHN blank NULL
JOHN NULL blank

In my mind these records are different and unique.

But it looks like they are not accordingly to you and "our pal" C.J.Date.

Thanks.