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

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



<JBoeker (AT) gmail (DOT) com> wrote