if/then query

[JBoeker]

Hello,

I have 3 tables in my mdb file, one contains a list of definitions, one
is a list physical items, and one is a list overrides. Normally my
query will join the definition physical item tables, sharing an id
number (e.g. in my defintion file I may have a table item and its
price, item number, etc; in my physical item table I may list sales
date, purchaser etc; the query result will combine these).

Sometimes I would like to override one of the definition columns with a
value in an override table. Is it possible to write an SQL query in
access that does this? I imagine something using the IIf operator.
Thanks,

Jeff

[HS Hartkamp]

Make a join of all three tables, and extract a column
Result: iif ( [tblOverride] is null;
[tblDefinition].[columnX];[tblOverride].[ColumnXorY])
(type this in the top-row of the bottom half of your query-design-view)

Make sure you have a left-join to the override table (arrow pointing to
override), because you'd miss records otherwise. To make sure, first select
your results, note the recordcount, and than add the override-table to your
query. If the recordcount is the same, it's OK.

Bas Hartkamp

<JB*****@gmail. com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

Hello,

I have 3 tables in my mdb file, one contains a list of definitions, one
is a list physical items, and one is a list overrides. Normally my
query will join the definition physical item tables, sharing an id
number (e.g. in my defintion file I may have a table item and its
price, item number, etc; in my physical item table I may list sales
date, purchaser etc; the query result will combine these).

Sometimes I would like to override one of the definition columns with a
value in an override table. Is it possible to write an SQL query in
access that does this? I imagine something using the IIf operator.
Thanks,

Jeff