[AccessD] Something too hard for me...

Hi Gustav,
In my language we use to say "I have no words to thank you..."
So, after two years using this access list I have to say that ADDPS is a
real great help community with no date and time limits.
Well, may be I couldn't manage to give you the right tips about my question.
I put your code line by line into a query but it gives back the same
calculation for all records.
There is no need to put a filter about the S because I want to know the Q of
all ID's with the same quantity.
My table contains records like:
ID   Q      PRICE
1     10     55,00
1     20     72,00
1     30     98,00
2     10     47,00
2     20     60,00
2     30     72,00
3     10     65,00
3     20     77,00
3     30     92,00
...
so, I can't use unique index to the fields Station and Quantity.
Running the query  e.g. for a 25 Q  it must answers

ID  Q    PRICE
1    25   88,30
2    25   62,00
3    25   92,30
...

Have a nice day
Kostas

--- 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 10:08 PM
Subject: Re: [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!
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>