Gustav Brock
gustav at cactus.dk
Sat Feb 7 06:56:41 CST 2004
Hi Kostas
> I am trying to create a little project to calculate some prices of a Radio
> Station advertising list
> In a table I keep CATEGORY and PRICE e.g.
10 -->> 55,00EURO
20 -->> 80,00
30 -->> 90,00
> bla, bla
> What I want to calculate is the price of an uncategorized spots between two
> categories.
> 25 SPOTS....
> The calculation is:
> A) the 20 spots cost 80,00
> B) I have to select the next category (in that case 30) to find out how much
> cost one spot (90,00/30 = 3,00EURO)
> C). the 5 spots cost 5X3,00=15,00
> so, the total price for the uncategorized query must be
> 80,00+15,00=95,00euro
You can, as an exercise in DMax(), do this with a single line of code
where Q is your number of spots:
curTotal = Nz(DMax("Price", "tblRate", "" & Q & " \ Quantity > 0"), 0) + Nz(DMax("(Price / Quantity) * (" & Q - Nz(DMax("Quantity", "tblRate", "Quantity < " & Q & ""), 0) & ")", "tblRate", "Quantity > " & Q & ""), 0)
The limitation is that your rate table must include an entry equal to
or exceeding the largest possible quantity of spots for a single
order.
Have fun!
/gustav