Related table or empty data field

[Gary Stark]

I have an issue to address that's causing me to loose what little haie I have left.

The scenario is that i have two tables - Table1 and Table2 - and they are linked on the contents of a field fCode which exists in both tables, so that a query on Table1 only returns data where there is a related entry with a matching fCode value in Table2.

So far, so good.

We need to understand that the data in Table2 is dynamic, and is in fact a linked table, attached to a .DBF file that can be changed at will by other processes. This part is not a problem, but it will only, and always, contain certain validated data, and the linked (source) table cannot be changed or have records added to it.

The problem I have is that there are certain records in Table1 that have an empty value in fCode. This is acceptable and by design, but the nature of the linking process (to Table2) excludes those records from the query.

Because I can't modify the source data, I cannot simply add a blank record to Table2.

The way I see it is that I have to try to formulate the linking of the tables in some way, along the lines of

if( len( [Table1]![fCode] ) < 1 Or ....

in the join statement of the SQL query, but I simply don't know how write this sort of code in Access.

Does anyone have any suggestions for how to write this query, or any other suggestions for how to somlve this problem?

Thanx in advance for any and all suggestions.

 

[Larry Linson]

Change the Link properties... in the top section of the Query Builder, click
the join line, then right-click and choose "Join Properties" -- one of the
options should be "All records from Table 1 and only those that match from
Table 2". The default (called an "equijoin" is "only those records that
match") is likely what you have and that is what is causing your difficulty.

Larry Linson
Microsoft Access MVP

I have an issue to address that's causing me to loose what little haie I have left.

The scenario is that i have two tables - Table1 and Table2 - and they are

linked on the contents of a field fCode which exists in both tables, so that
a query on Table1 only returns data where there is a related entry with a
matching fCode value in Table2.

So far, so good.

We need to understand that the data in Table2 is dynamic, and is in fact a

linked table, attached to a .DBF file that can be changed at will by other
processes. This part is not a problem, but it will only, and always, contain
certain validated data, and the linked (source) table cannot be changed or
have records added to it.

The problem I have is that there are certain records in Table1 that have

an empty value in fCode. This is acceptable and by design, but the nature of
the linking process (to Table2) excludes those records from the query.

Because I can't modify the source data, I cannot simply add a blank record to Table2.

The way I see it is that I have to try to formulate the linking of the

tables in some way, along the lines of

if( len( [Table1]![fCode] ) < 1 Or ....

in the join statement of the SQL query, but I simply don't know how write this sort of code in Access.

Does anyone have any suggestions for how to write this query, or any other

suggestions for how to somlve this problem?

 

[Gary Stark]

Larry,

Thanx for the prompt response, but I'm not sure that's quite what I want.

options should be "All records from Table 1 and only those that match from
Table 2".

What I want is something along the lines of "only those that match from Table 2 plus all those from Table 1 with no value in fCode."

Thus, I still want to exclude those records in Table 1 that have a non-matching value with Table 2.

The default (called an "equijoin" is "only those records that
match") is likely what you have and that is what is causing your difficulty.

I think the difficulty is the fact that I want to have matched records plus records that have no data in the field in question.

Thanx again ...

g.
Gary Stark.

Larry Linson
Microsoft Access MVP

I have an issue to address that's causing me to loose what little haie I have left.

The scenario is that i have two tables - Table1 and Table2 - and they are

linked on the contents of a field fCode which exists in both tables, so that
a query on Table1 only returns data where there is a related entry with a
matching fCode value in Table2.

So far, so good.

We need to understand that the data in Table2 is dynamic, and is in fact a

linked table, attached to a .DBF file that can be changed at will by other
processes. This part is not a problem, but it will only, and always, contain
certain validated data, and the linked (source) table cannot be changed or
have records added to it.

The problem I have is that there are certain records in Table1 that have

an empty value in fCode. This is acceptable and by design, but the nature of
the linking process (to Table2) excludes those records from the query.

Because I can't modify the source data, I cannot simply add a blank record to Table2.

The way I see it is that I have to try to formulate the linking of the

tables in some way, along the lines of

if( len( [Table1]![fCode] ) < 1 Or ....

in the join statement of the SQL query, but I simply don't know how write this sort of code in Access.

Does anyone have any suggestions for how to write this query, or any other

suggestions for how to somlve this problem?

Thanx in advance for any and all suggestions.

 

[John Vinson]

The problem I have is that there are certain records in Table1 that have an empty value in fCode. This is acceptable and by design, but the nature of the linking process (to Table2) excludes those records from the query.

Because I can't modify the source data, I cannot simply add a blank record to Table2.

You can't link on NULLs anyway so it wouldn't help!

The way I see it is that I have to try to formulate the linking of the tables in some way, along the lines of

if( len( [Table1]![fCode] ) < 1 Or ....

I think you need a Right Outer Join: all records in Table2 plus
matching records in Table1. If you apply criteria to this query you'll
only see those records that meet the criteria in Table1; you'll see
the matching Table2 record if there is one.

 

[Gary Stark]

John,

Thanx for the input.

What I ended up using though was a left join.

SELECT TABLE1.FCODE, TABLE1.FIELD1, TABLE1.FIELD2, FROM TABLE1
LEFT JOIN TABLE2 ON TABLE1.FCODE = TABLE2.FCODE
WHERE (((TABLE1.FCODE) Is Null) AND ((TABLE2.FCODE) Is Null)) OR (((TABLE1.FCODE) Is Not Null) AND (([TABLE1]![fcode])=[TABLE2]![fcode]));

This seems to be giving me exactly what I want.

Thanx y'all for the help. Muchly appreciated.

The problem I have is that there are certain records in Table1 that have an empty value in fCode. This is acceptable and by design, but the nature of the linking process (to Table2) excludes those records from the query.

Because I can't modify the source data, I cannot simply add a blank record to Table2.

You can't link on NULLs anyway so it wouldn't help!

The way I see it is that I have to try to formulate the linking of the tables in some way, along the lines of

if( len( [Table1]![fCode] ) < 1 Or ....

I think you need a Right Outer Join: all records in Table2 plus
matching records in Table1. If you apply criteria to this query you'll
only see those records that meet the criteria in Table1; you'll see
the matching Table2 record if there is one.