By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 2,093 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

if/then query

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

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.googlegr oups.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

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.