If statement in access queries in choosing 2 prices from 1 product based on Quantity Ordered

Question

So, lets say I have 3 tables. First table is the product table where it consists of

Prod_code, Prod_name, Prod_qty, Prod_Price1, Prod_Price2.

The second table is the Order table where it consists of

Order_ID, Order_Date, Total_price.

And the last table is the order detail table which consists of

Order_Detail_ID, Order_ID (foreign key from order table), Prod_code(foreign key from Product table), Qty_Ordered, and Price

If you can see I have 2 prices for each product in the product table for the purpose of:

1. If the Qty_ordered from the order detail table is greater than 10 unit, then the Price column in the order detail given will be Prod_price2
2. If the Qty_ordered from the order detail table is equal to or less than 10 unit, then Price column in the order detail given will be Prod_price1

How can I make this happen so when I create an order detail form, it can automatically give me the Product price based on the Qty_ordered that I input? I believe this could require some if statements in the query but I'm a novice in Ms. Access and I need you guys' help. Thanks a lot

Answer 1

In a query that joins Orders and Products to OrderDetails, calculate a field with:
IIf(Qty_Ordered <= 10, Prod_Price1, Prod_Price2)

If you need to display this on data entry form bound to OrderDetails, build a combobox that lists products and has both prices as columns (can be hidden if you want). An expression in textbox can reference columns of combobox by their index. So if Price1 is in third column its index is 2.
=IIf(Qty_Ordered <= 10, Me.cbxProduct.Column(2), Me.cbxProduct.Column(3))

Saving this calculated result will require code (macro or VBA) in some event, probably form BeforeUpdate.
Me!Price = Me.tbxPrice