query substitution

[Jeff]

Hello,

I have two tables, both containing the same field name, e.g. table 1
has Field1, Field2, Field3 and and table 2 has Field1 and Field3. I
would like to do a query that returns all the records from table 1,
substituting table 2's Field3 for table 1's Field3 when Field1 is the
same. The records of table 2 are a subset of table 1.

Currently, I can list all the records as below and have to
programmatically substitute tblTwo.Field3 for tblOne.Field3 when
tblTwo.Field3 has a value in it.

SELECT tblOne.Field1, tblOne.Field2, tblOne.Field3, tblTwo.Field3
FROM tblOne LEFT JOIN tblTwo ON tblOne.Field1=tblTwo.Field1;

Thanks,

Jeff

 

[Brian]

Hello,

I have two tables, both containing the same field name, e.g. table 1
has Field1, Field2, Field3 and and table 2 has Field1 and Field3. I
would like to do a query that returns all the records from table 1,
substituting table 2's Field3 for table 1's Field3 when Field1 is the
same. The records of table 2 are a subset of table 1.

Currently, I can list all the records as below and have to
programmatically substitute tblTwo.Field3 for tblOne.Field3 when
tblTwo.Field3 has a value in it.

SELECT tblOne.Field1, tblOne.Field2, tblOne.Field3, tblTwo.Field3
FROM tblOne LEFT JOIN tblTwo ON tblOne.Field1=tblTwo.Field1;

Thanks,

Jeff

SELECT tblOne.Field1, tblOne.Field2, Nz(tblTwo.Field3, tblOne.Field3) Field3
FROM tblOne LEFT JOIN tblTwo ON tblOne.Field1=tblTwo.Field1;

 

[Ken Snell [MVP]]

Try this:

SELECT tblOne.Field1, tblOne.Field2,
IIf(Len(tblTwo.Field3 & "")=0, tblOne.Field3, tblTwo.Field3) AS TheField3
FROM tblOne LEFT JOIN tblTwo ON tblOne.Field1=tblTwo.Field1;

 

[Jeff]

Thanks a lot, Brian, Ken. It works like a charm. One minor
inconvience is that even though tblOne.Field3 and tblTwo.Field3 are
both defined identically as a drop down combo box (which replaces the
numeric value with a text label for display purposes), the resultant
query jost shows a raw number. This isn't too big a deal since I use
the query programmatically, not within Access. I'm just curious.

Jeff

 

[Brian]

Thanks a lot, Brian, Ken. It works like a charm. One minor
inconvience is that even though tblOne.Field3 and tblTwo.Field3 are
both defined identically as a drop down combo box (which replaces the
numeric value with a text label for display purposes), the resultant
query jost shows a raw number. This isn't too big a deal since I use
the query programmatically, not within Access. I'm just curious.

Jeff

You are confusing data with presentation. A field in a table cannot be
defined as a combo box, a combo box is a control type, not a data type.

It is possible that you have the lookup properties for the fields in the
table set to combo box. However, this does not alter the fact that the data
stored in the table is numeric, it merely causes Access to display a combo
box instead of the actual data. This latter point (that you can't see the
actual data in the table) is a Very Bad Thing, and most serious Access
people would NEVER set the lookup properties of a table field to Combo Box.
Personally, I believe it should be a capital offence ;-^