Joning on null value

  • Thread starter Thread starter Marcie
  • Start date Start date
M

Marcie

I have one table that contains city state and zip. I have created another
table that has each of the unique values of above and another "code". One of
the unique values is null, null, null, 4. What I want to do is bring in that
value to a query that joins the above 2 and have a field "code" = 4 when it
is null.

Although I know I could just do an if for that it goes further as the 3
fields could have any combinations of null values and based on each unique
value I need to populate that "code".

so basically my question is this can you do a join that would be null, null
null = null, ,null, null and make it match?
 
I can not follow what would be in the tables and how to join.
Post sample data of each table and which records would be joined.
 
You can never JOIN on NULL values. Null never equals anything else and
when used in any comparison returns NULL. Matches must evaluate to TRUE.

What you M I G H T be able to do is to match using NZ.

ON NZ(Table1.City,"NoSuchPlace") = Nz(Table2.City,"NoSuchPlace"
AND NZ(Table1.ZIP,"00000") = NZ(Table2.Zip,"00000")
AND Nz(Table1.State,"RR") = NZ(Table2.State,"RR")




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Table1
Record City State Zip
1 SanDiego CA 99916
2 SanAntonio TX 87255
3 SanDiego CA 99916
4 blank blank blank
5 La Crosse WI blank
6 Sandieago CA blank
7 La Crosse WI blank

so I created a table with each "unique" occurence
Table2
ID
1 SanDiego CA 99916
2 SanAntonio TX 87255
4 blank blank blank
5 La Crosse WI blank
6 Sandieago CA blank

I would then want to join table 1 to table 2 where City State and zip
match and return table 2 id. so once joined I would have a query result that
say blank blank blank 4, which I would then know means all 3 fields were
blank.
 
Back
Top