Results 1 to 8 of 8
  1. #1
    RJLLodigiani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    4

    Question Having problems with Nulls in joins

    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    You cant match nulls. They dont exist, so they cant match.

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

  3. #3
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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:
    Code:
    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).

  4. #4
    RJLLodigiani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    4
    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!

  5. #5
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    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!

  6. #6
    RJLLodigiani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    4
    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?

  7. #7
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    I don't think it is a good idea to force Unique Indexes on fields that are not required.

  8. #8
    RJLLodigiani is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Posts
    4
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Nulls problem, hmm
    By redbull in forum Programming
    Replies: 6
    Last Post: 06-29-2012, 12:16 PM
  2. sql problems with multiple inner joins
    By mejia.j88 in forum Queries
    Replies: 1
    Last Post: 01-03-2012, 05:41 PM
  3. SUM in regards to nulls
    By detlion1643 in forum Access
    Replies: 5
    Last Post: 02-03-2010, 08:50 AM
  4. Replies: 2
    Last Post: 01-18-2010, 11:52 AM
  5. GetRows - Invalid Use of Nulls
    By Wannabe_Pro in forum Programming
    Replies: 3
    Last Post: 07-22-2009, 07:07 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums