Multiple IIf and Multiple Calculation in query

[Arnold]

Hello All,

My DB has Items in [Categories] x,y, and z.
Each has a unique mark up over the the [cost]
x = *1.20


y = *1.25
z = *1.30

Depending on the [cost] I would like to add an additional perc.
<50 = .20
>50<200 = .10
>200 = .05

After the above calculation I would like to add [shipping],(and Round to to 2 decimals)

SO ItemA [cost] (55.00) that belongs to [categories]z and [shipping](12.00)
(55*1.40)+12.00 = 89.

How would I calculate this?

Thanks in advance

[rpeare]

something like:

([cost] * ([CategoryMarkup] + iif(cost <50, .2, iif(cost > 200, .05, .1)))) + [ShippingCharge]

check the bracketing though I'm not sure I got it right.

[Perceptus]

Given your data.
55*z + additional comes to 7.15 for me. can you verify?

[Arnold]

Thank you

If I would like to add multiple IIf to this
[cost]<50 and [categories]y IIf [cost]>200 [categories]x etc.
how would I do it?

[rpeare]

You have two variables driving the end cost of your item.

The category (x, y and z)
The Cost of the original item (broken out by cost)

If you can define your categories for the COST based percentage in a TABLE like (I assume) you are doing with your categories (x, y and z)

you do not need multiple IIF's to account for every combination.

your category is adding a percentage (.2, .25 and .3) and your original item cost is adding a percentage (.2, .1 and .05) the formula I gave you will work assuming you only have three price range categories, if you have more than 3 I would build a table supporting that structure that you can change over time rather than having to change your formulas everywhere they exist.

The formula assumes CATEGORYMARKUP is your 1.2, 1.25 or 1.3, the part of the formula that reads iif(cost <50, .2, iif(cost > 200, .05, .1)) is figuring out the cost bracket percentage increase, when the original cost is multiplied by this you should get the proper number

In your case (55 * (1.3 + .1)) + [ShippingCharge] or (55 * 1.4) + [ShippingCharge] ====> 77 + [ShippingCharge]

[Arnold]

Thanks

I think I got it.