If function in queries

  • Thread starter Thread starter Natalie
  • Start date Start date
N

Natalie

Hi - I am trying to create a query that will calculate the
commission on a sale price, the commission is dependant on
whether the price is over £2000 or not. If it is the
commission is 15% up to £2000 and 10% over it, otherwise
it is just 10%. I think the code for this calculation is
as shown below but I have no idea how to create this
field - can I even do it in a query? I tried to do it in
a form on the after update property of the price box but
it keeps changing the commission all records rather than
allowing different values for different sales. Can anyone
help?
I used this code in my form!:

If [HammerPrice] >= 2000 Then
[Comm] = ([HammerPrice] / 10) + 300
Else
[Comm] = [HammerPrice] * 0.15
End If

Thanks - Natalie
 
Hi,


SELECT iif(HammerPrice>=2000, 0.1, 0.15) * HammerPrice +
iif(HammerPrice>=2000, 200, 0) As Comm

FROM ...



Note that if you have more than just one step, better to use another table
to describe the steps:

CommSteps ' table name
StepLimit ToStep StepPC StepOffset ' fields
0 2000 .15 0
2000 10000 .10 300
10000 100000 .075 600 ' values


then


SELECT HammerPrice*StepPC + StepOffset As Comm
FROM Hammers INNER JOIN CommSteps
ON (Hammers.HammerPrice >=CommSteps.StepLimit)
AND
(Hammers.HammerPrice < CommSteps.ToStep)


Note that I strongly suspect the exact formula is more like:


SELECT (HammerPrice-StepLimit)*StepPC + StepOffset As Comm
FROM Hammers INNER JOIN CommSteps
ON (Hammers.HammerPrice >=CommSteps.StepLimit)
AND
(Hammers.HammerPrice < CommSteps.ToStep)

ie:

If HammerPrice >= 2000 then
comm = (hammerPrice-2000)*0.10 + 300
Else
comm = hammerPrice*.15
End If

so that, at hammerPrice = 2000, both formula return the value 300.



Having the values in a table rather than in the "code", they are easier to
change!


Hoping it may help,
Vanderghast, Access MVP


Hi - I am trying to create a query that will calculate the
commission on a sale price, the commission is dependant on
whether the price is over £2000 or not. If it is the
commission is 15% up to £2000 and 10% over it, otherwise
it is just 10%. I think the code for this calculation is
as shown below but I have no idea how to create this
field - can I even do it in a query? I tried to do it in
a form on the after update property of the price box but
it keeps changing the commission all records rather than
allowing different values for different sales. Can anyone
help?
I used this code in my form!:

If [HammerPrice] >= 2000 Then
[Comm] = ([HammerPrice] / 10) + 300
Else
[Comm] = [HammerPrice] * 0.15
End If

Thanks - Natalie
 
Back
Top