Calculating a QTY discount

[Guest]

Hi,

I have a query from a couple of tables that includes ItemNumber, QtySold,
and SalesPrice. I want to find all instances where a client bought more that
24 of an item and to calculate a 10 percent discount. The discount is only
on the first 24 of items sold unless it reaches 48, 72 or 96. Then it is on
the full amount. So if a client bought 30 of an item that was $10, the
amount sold should be $216 ($240 - $24) + $60 (6 x $10) or $276. If they
bought 48 of the item, the cost would be $432 ($480 - $48). Is there a way
to program a query so that it calculates this?

Thanks,

 

[Guest]

TotPrice: ((([qty]\24) * 24) * ([item_price] - [item_price] * .1)) + (([qty]
mod 24) * [item_price])

The \ operator will return the number of times the divisor will go evenly.
so 30 \ 24 = 1
then the mod operator will show the remainder

 

[Guest]

The discount is only on the first 24 of items sold unless it reaches 48, 72
or 96.
What is to happen at 48, 72, or 96?
Seems like the discount needs to apply on all OVER 24.
Here is as you stated ---
SELECT ChuckW.ItemNumber, ChuckW.QtySold, ChuckW.SalesPrice,
IIf([QtySold]>=24,(24*([SalesPrice]*0.9))+(([QtySold]-24)*[SalesPrice]),[QtySold]*[SalesPrice]) AS Total
FROM ChuckW
ORDER BY ChuckW.QtySold;

ItemNumber QtySold SalesPrice Total
1 14 10 140
5 23 10 230
4 24 10 216
6 25 10 226
3 26 10 236
2 27 10 246
1 28 10 256
5 29 10 266
4 30 10 276
6 31 10 286

 

[Guest]

Hi,

Thanks for your help. This suggestion worked. I have a follow up question.
We have about five clients out of 100 that are ineligible for this case lot
discount. The still purchase in cases (24, 48 etc) but don't get the 10
percent discount. I was to include them into my analysis but I want to
exclude them from getting this disount. My query has CustomerName,
ItemNumber, QTY, Item_Price and Amount. Is there a way to do this?

Thanks,
--
Chuck W

TotPrice: ((([qty]\24) * 24) * ([item_price] - [item_price] * .1)) + (([qty]
mod 24) * [item_price])

The \ operator will return the number of times the divisor will go evenly.
so 30 \ 24 = 1
then the mod operator will show the remainder

--
Dave Hargis, Microsoft Access MVP

Hi,

I have a query from a couple of tables that includes ItemNumber, QtySold,
and SalesPrice. I want to find all instances where a client bought more that
24 of an item and to calculate a 10 percent discount. The discount is only
on the first 24 of items sold unless it reaches 48, 72 or 96. Then it is on
the full amount. So if a client bought 30 of an item that was $10, the
amount sold should be $216 ($240 - $24) + $60 (6 x $10) or $276. If they
bought 48 of the item, the cost would be $432 ($480 - $48). Is there a way
to program a query so that it calculates this?

Thanks,