Kostas Konstantinidis
kost36 at otenet.gr
Sun Feb 8 14:32:28 CST 2004
Hi Gustav,
thank's a lot
Everything works perfect
Kostas
----- Original Message -----
From: "Gustav Brock" <gustav at cactus.dk>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Sunday, February 08, 2004 12:07 PM
Subject: Re: [AccessD] Something too hard for me...
> Hi Kostas
>
> > 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.
>
> You are welcome! That is was the list is for.
>
> > 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.
>
> The index was only meant to prevent duplicate entries as the
> combination of Station ID and Quantity must be unique.
>
> > 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
> > ...
>
>
> OK. You can include the expression in a query where it will be run
> record by record with the respective Station ID.
> Note the correction of Format() to round to one decimal:
>
> <SQL>
>
> PARAMETERS
> Q Short;
> SELECT DISTINCT
> Station,
> [Q] AS Quantity,
> Format(
> Nz(DMax("Price", "tblRate", "Station = " & Station & " And " & Q & "
\ Quantity > 0"), 0) +
> Nz(DMax("(Price / Quantity) * (" & Q -
> Nz(DMax("Quantity", "tblRate", "Station = " & Station & " And
Quantity <= " & Q & ""), 0)
> & ")", "tblRate", "Station = " & Station & " And Quantity > " & Q &
""), 0),
> "0.0" & "\0") AS Total
> FROM
> tblRate;
>
> </SQL>
>
> Copy and paste this into a new query and adjust to your naming of
> fields etc.
>
> However, if this - to build a list - is the purpose, and the DMax()
> exercise hadn't be done or your count of stations were 1900 and not
> 19, I would suggest to follow the approach of Drew and build a query
> and calculate in SQL, though - with the needed in-line or sub select
> queries - it wouldn't be much nicer to watch.
>
> /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 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
>