1

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

enter image description here

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.

HansUp
  • 95,961
  • 11
  • 77
  • 135
Ryan Cassidy
  • 29
  • 2
  • 7

2 Answers2

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)
Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • So I would just use a SELECT statement to select the fields, and where would I put this statement? – Ryan Cassidy Dec 19 '13 at 16:28
  • SELECT OrderDetailsT.SandwichQuantity, OrderDetailsT.SandwichPrice, OrderDetailsT.Discount FROM OrderDetailsT WHERE (((OrderDetailsT.Discount)=IIf(("SandwichQuantity * SandwichPrice")>50,30,0))); – Ryan Cassidy Dec 19 '13 at 16:36
  • SELECT OrderDetailsT.SandwichQuantity, OrderDetailsT.SandwichPrice, IIf((SandwichQuantity * SandwichPrice)>50,30,0) as Discount FROM OrderDetailsT WHERE (SandwichQuantity * SandwichPrice)>50; – Johnny Bones Dec 19 '13 at 16:41
  • Another odd thing, it doesn't update the record in the OrderDetailsT. It only does it when I use the query, so by default the discount is whatever the customer enters. [link]http://cl.ly/T21b[link] – Ryan Cassidy Dec 19 '13 at 16:51
  • You would need to use an Update query if you wanted to make the changes permanent. – Johnny Bones Dec 19 '13 at 18:25
0

If this is a data entry screen You can add an After_Update Event to Sandwich Price And Sandwich Quantity

Private Sub SandwichPrice_AfterUpdate()
   update_total 
End Sub

Private Sub SandwichQuantity_AfterUpdate()
   update_total 
End Sub
Private Sub update_total()
   Dim total AS Double
   If IsNull(Me.SandwichPrice) OR IsNull(Me.SandwichQuantity) Then Exit Sub
   total = Me.SandwichPrice * Me.SandwichQuantity
   SELECT CASE total
       CASE 51 to 1000
          Me.Discount = 0.3
       CASE Else
          Me.Discount = 0
   END SELECT
   Me.TotalPrice = total - (total * Me.Discount)
End Sub

Doing it this way will allow you to add multiple tiers of discount by adding CASE Statements i.e. You could add CASE 10 TO 20 with a new discount and it will apply when TotalPrice is >=10 And <= 20.

This will allow your lookup query to return the correct values from discount. I don't think this should be handled in the lookup query but rather in the data storage itself.

engineersmnky
  • 25,495
  • 2
  • 36
  • 52
  • Where would I implement this? I'm not familiar with VB. – Ryan Cassidy Dec 19 '13 at 16:37
  • On the entry form open in Design view Right click on SandwichQuantity TextBox -> Properties. In the Properties Window goto the Event Tab Click on the AfterUpdate field -> CodeBuilder repeat for SandwichPrice. Paste update_total on the same screen – engineersmnky Dec 19 '13 at 16:42