Link Two Tables - Missing Information

[learning_codes]

Hi,

I need your feedback. I have two tables. One of the two tables has
missing information. Your help would be much appeciated.

When I try to select "Include All Records from Table #1 and those
records from Table #2 where the joined fields are equal" for two fields
(City) and (State). I'm having a problem with "BLANK" from Table #1
does not pick up from Table #2.

Table #1:
Field: City Field: State Field: CountryCode
-blank- New York USA
Dallas Texas USA
Buffalo New York USA


Table #2:
Field: City Field: State Field: CountryName
-blank- New York United State
Dallas Texas United State
Buffalo New York United State


I want to see the output of the Query:

Field: City Field: State Field: CountryCode Field:
CountryName
-blank- New York USA
United State
Dallas Texas USA
United State
Buffalo New York USA
United State

but I got the result of the output from the Query. I want to see the
output of the Query:

Field: City Field: State Field: CountryCode Field:
CountryName
-blank- New York USA
-Blank-
Dallas Texas USA
United State
Buffalo New York USA
United State

 

[Tom Lake]

Hi,

I need your feedback. I have two tables. One of the two tables has
missing information. Your help would be much appeciated.

When I try to select "Include All Records from Table #1 and those
records from Table #2 where the joined fields are equal" for two fields
(City) and (State). I'm having a problem with "BLANK" from Table #1
does not pick up from Table #2.

Table #1:
Field: City Field: State Field: CountryCode
-blank- New York USA
Dallas Texas USA
Buffalo New York USA


Table #2:
Field: City Field: State Field: CountryName
-blank- New York United State
Dallas Texas United State
Buffalo New York United State


I want to see the output of the Query:

Field: City Field: State Field: CountryCode Field:
CountryName
-blank- New York USA
United State
Dallas Texas USA
United State
Buffalo New York USA
United State

but I got the result of the output from the Query. I want to see the
output of the Query:

Field: City Field: State Field: CountryCode Field:
CountryName
-blank- New York USA
-Blank-
Dallas Texas USA
United State
Buffalo New York USA
United State

You need to choose all records from Table 2 and only those from Table
where the fields are equal.

Tom Lake

 

[John Vinson]

I need your feedback. I have two tables. One of the two tables has
missing information. Your help would be much appeciated.

When I try to select "Include All Records from Table #1 and those
records from Table #2 where the joined fields are equal" for two fields
(City) and (State). I'm having a problem with "BLANK" from Table #1
does not pick up from Table #2.

Table #1:
Field: City Field: State Field: CountryCode
-blank- New York USA
Dallas Texas USA
Buffalo New York USA

If you're trying to match NULL city to NULL city, you'll indeed find
no matches. Null doesn't match *anything*, even another NULL.

One question: what if there are multiple rows in Table2 with a NULL in
the city field? How should they be matched? Would you want to join by
State and CountryCode as well?

What you might want to do is run an Update query on both tables,
updating any NULL city fields to some constant value such as

(No City Entered)

This will let you join (provided there aren't multiple such rows!)


John W. Vinson[MVP]