[AccessD] Something too hard for me...

Hi Kostas

> Hi Gustav,
> thank's a lot for your really excellent job.
> It works perfect but I have another question which is very critical.

> we are a network of 19 Regional Greek Stations so the general advertising
> list incloudes common Quantities but different prices for every of them.
> Every Station based on its ID (from 1 to 19)
> I tried to put your code on but it conficts with good reason cause the same
> quantities are iclouding several times
> Michael and Drew Many thank's for your time too

> Be well


This is possible and still fun but before being slammed by my fellow
listers, let me note that we are approaching the land of unreadable
code. Nevertheless:

curTotal = Nz(DMax("Price", "tblRate", "Station = " & S & " And " & Q & " \ Quantity > 0"), 0) + Nz(DMax("(Price / Quantity) * (" & Q - Nz(DMax("Quantity", "tblRate", "Station = " & S & " And Quantity <= " & Q & ""), 0) & ")", "tblRate", "Station = " & S & " And Quantity > " & Q & ""), 0)

In addition to the inclusion of a Station ID, please note a minor
correction (<=).

Your table should now as minimum read:

  Station: Integer
  Quantity: Integer
  Price: Currency

A unique index should be applied to the fields Station and Quantity.
Also, you may need to round the total:

curTotal = Format(Nz(DMax("Price", "tblRate", "Station = " & S & " And " & Q & " \ Quantity > 0"), 0) + Nz(DMax("(Price / Quantity) * (" & Q - Nz(DMax("Quantity", "tblRate", "Station = " & S & " And Quantity <= " & Q & ""), 0) & ")", "tblRate", "Station = " & S & " And Quantity > " & Q & ""), 0), "Standard")

/gustav


> ----- Original Message ----- 
> From: "Gustav Brock" <gustav at cactus.dk>
> To: "Access Developers discussion and problem solving"
> <accessd at databaseadvisors.com>
> Sent: Saturday, February 07, 2004 2:56 PM
> Subject: Re: [AccessD] Something too hard for me...


>> 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!