Access SQL - If TotalPrice is > 30, Discount = 30%

Question

I'm working on an access database at the moment, where I have multiple fields.

• Product Quantity (Entered by User)
• Product Price (Retrieved by Database)
• Total Price (Product Quantity * Product Price)
• Discount

Discount needs to be calculated, however I don't know how to set it to 30% on the condition that TotalPrice is more than 50. It would be useful if the TotalPrice automatically updated if it was more than 50 with the discount too.

Answer 1

Set up a new query that brings in Product Quantity, ProductPrice and TotalPrice. Then, in the Design View of the query, add this field:

Discount: IIF((ProductQuantity * ProductPrice) > 50, 30, 0)

If you're not familiar with IIF statements, the above reads: "If ProductQuantity times ProductPrice is greater than 50, then set Discount = 30, otherwise set Discount = 0"

This will set your discount = 0 if Total Price is less than or equal to 50, so edit that last part if it needs to be something else. Also, I made assumptions on your field names, so you may need to tweak those too, but you get the idea.

If you need Discount to actually reflect 30% of Total Price, then if would look like this:

Discount: IIF((ProductQuantity * ProductPrice) > 50, (ProductQuantity * ProductPrice) * .30, 0)