Putting another query as a result of an iif statement

[stickers]

Can anyone help with this one? I have three tables: tblListFFE - is a schedule of items of furniture in rooms
tblGeneric - generic items (chair) -
tblProducts - manufacturers specific products

tblListFFE has a Yes/No field for 'Specific Product selected' (I'll call it 'Spec') It is linked to tblGeneric by the ItemID field, and to tblProduct by the ProdID field. tblGeneric and tblProduct are linked by the ItemID field There may be several specific product options for each generic item.

Both tblGeneric and tblProduct have costs against each item. Obviously in tblGeneric they are rough estimates

Basically, I want to compile a query that shows all the items in tblListFF&E with their working costs i.e. if the Spec field has been ticked, the query picks up the cost from tblProduct, and if not, it takes it from tblGeneric.

I'm sure there must be a neat way to do this. Currently I have had to create 3 queries. One lists all items with their costs from tblProduct if the Spec field is Yes, and zero as a cost if the Spec field is No:

SELECT tblListFFE.ListID, tblListFFE.ItemID, IIf(tblListFFE!SpecSelected=True,0,tblGeneric!GCost) AS GenCost
FROM tblGeneric RIGHT JOIN tblListFFE ON tblGeneric.ItemID = tblListFFE.ItemID
ORDER BY tblListFFE.ListID;

The second one which pulls costs from tblGeneric if Spec = No and zero cost if Spec = Yes:

SELECT tblListFFE.ListID, tblListFFE.ItemID, IIf(tblListFFE!SpecSelected=True,tblProducts!Cost,0) AS ProdCost
FROM tblProducts RIGHT JOIN tblListFFE ON tblProducts.ProductID = tblListFFE.ProductID
ORDER BY tblListFFE.ListID;

The third query just combines these two by adding together the two cost fields. Clumsy but it works. Can anyone show me a better way to do this?

Sorry for being longwinded!! ;-)

 

[RickSpr]

If tblGeneric.ItemID is the primary key or has a unique index, and the same is true of tblProducts.ProductID, there is a single-query way to do this.

This constraint is necessary to ensure that only one row at most from each of those tables matches with a row in tblListFFE. When that's true, you can do a 3-table join without getting duplicates of rows in tblListFFE.

The query contains all three tables, with outer joins from tblListFFE to each of the other two tables. For the Cost output column, use the expression:
IIf(tblListFFE.SpecSelected, tblProducts.Cost, tblGeneric.Cost)

If the primary key/unique index constraint doesn't hold, your solution (or any of several slight variations) is the best, I believe. Rick Sprague
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein