linking fields which are blank

[MetLife]

Hi,

I linked two tables with a "join" and the resulting table excludes the record if the value in both table A and table B is blank.

At least this is what it appears to be doing.

Is there a way around this (to include the record) because in this case the fields are the same but they are blank.

Thanks

 

[Joe4]

You cannot join on a blank field in any database program.
Fields that you are joining on really should not have any blanks.

You can do LEFT or RIGHT joins, which will include ALL records from one of the tables, and all matches from the other. So it would show the blanks from the one table. But that will not return/join blank fields from the other table to that.

 

[MetLife]

OK thanks for that clarification

If you get blank data, do you normally fill it with "NA" using a macro then? The query will not work with LEFT or RIGHT joins because the fields have to match exactly.

Thanks

 

[MetLife]

when I use a LEFT or RIGHT join, I get the error message - the sql statement could not be executed because it contains ambiguous outer joins. To force one of the joins to be performed first, create a separeate query that performs the first join and then include that query in your sql statement.

 

[Joe4]

It all depends on what has to happen (what you are trying to do with it).
Note that if you have multiple blanks in one file, filling them with the same value with create one-to-many or many-to-many relationships. That could be problematic, depending on what you want to do (your query could return more records than are found in either table).

Doesn't some sort of determination need to be make which records from table A should match up to which records in table B?

 

[xenou]

Post some sample data of what you have in the tables that you are trying to join.

 

[MetLife]

What is supposed to happen, is that the columns represent a unique lookup key. So table A is assets and table B is liabilities and they have to match up using several columns from A and B. I think this is a one to one relationship.

This was done in matlab, where the key values were combined with a separator "|" between them to create a lookup key.

 

[Joe4]

You can join two tables on one field or multiple fields. However, none of the fields you are joining on should contain any blanks. That will create problems.

 

[MetLife]

OK gotcha - thanks!