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