Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting Which Records to Sum etc. (Access 2003)

    I have a subform (based on a query) which I am using to record plant diseases and dates they were treated. I then want to create various aggregate calculations. My problem is that some plant batches appear several times and I'm getting double counting. In the hope that a picture is really worth a thousand words, I'm attaching a screen shot which I hope helps explain what I'm trying to do. I have had several goes at rewriting the query and the calculated controls on the form with no success.

    All assistance most gratefully received.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    Could you explain what you want to count, and where the result differs from what you expect? Your screenshot doesn't tell me that.

  3. #3
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    Hi Hans

    The problem is with batch 1 (and anything else with multiple entries):

    Total lost = okay
    Total left = This is NoPlanted-NoLost. So what I am getting is day 1: (NoPlanted = 200)-(NoLost = 10) = 190; day 2: (NoPlanted = 200)-(NoLost = 20) = 180. When what I really need is day 2: (TotalLeft = 180)-(NoLost = 20) = 160
    Total cost = sums all entries for batch 1 when the total cost is

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    I'm sorry, I don't understand your explanation of the "Total left" calculations. How should I be able to see that day2 should be 180 - 20? Where does the 180 come from?

  5. #5
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    Hi Hans

    Apologies. Messed up my sums. I'll try again...

    I need to calculate:
    If a plant batch starts off at 200 items and 10 are lost then there are 190 left.
    A while later a further 20 are lost so that leaves 170

    I then want to pick up this 170 left and use it in other calculations

    However I can't make my query do this.

    Plant loses are entered on the subform as they occur by date. If there is more than one entry because plants have been lost on several different days. I am getting each record entered again without reference to what has gone before.

    Plant batch = 200 items and loses 10 leaving 190
    A while later there are another 20 losses but these are still taken off the original 200 leaving 180

    Then when I try to pick up the number of plants left in the batch, I am summing all the plant left entries for the batch meaning for the example above I would get 370 plants left.

    Hope I have explained it a bit better this time.

    All the best

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    The solution will probably involve the DSum function: DSum("Field-or-expression", "Table-or-query", "Optional where-condition") will calculate the sum of the values of the field (or expression) for all records from the table (or query) satisfying the where-condition (if specified).

    Since I don't know the exact table and field names involved, I can only give a general idea:

    NoLeft: [NoPlanted] - DSum("NoLost", "tblBatches", "[DateField] <= #" & Format([DateField], "mm/dd/yyyy") & "#")

    The expression subtracts all losses up to and including the date in the date field from the number planted. You will have to substitute the correct names.

  7. #7
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    Hi Hans

    I was semi-successful with this. The field is now working as a running sum but it is not taking the different batch numbers into account. I tried to concatenate two fields:

    NoLeft: [NoPlanted]-DSum("NoLost","tbl_PlantTreatment","[BatchRef]&<treatmentDate><=[BatchRef]&#" & Format(<treatmentDate>,"mm/dd/yyyy") & "#")

    I hope this code comes out properly. For some reason it is missing the TreatmentDate field out in both places in Preview

    which 'runs' as far as the syntax is concerned but the figures it produces are complete rubbish.

    Thanks
    Jill

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    Could you edit your post? The word TreatmentDate between square brackets is interpreted as a special code by the lounge software. You can get around this by using <!t>[t]<!/t> to produce an opening bracket and <!t>[/t]<!/t> to produce a closing bracket: <!t>[t]<!/t>TreatmentDate<!t>[/t]<!/t> in the edit window becomes <!t>[TreatmentDate]<!/t> when previewed and posted.

  9. #9
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting Which Records to Sum etc. (Access 2003)

    Hi Hans

    I have found a solution. I went back to my text books, lecture notes and the MSAccess Help and read them all again and this time the penny dropped on how to create aggregate values on a subform and call them from the main form footer. So because of the parent/child relationship this sorted out the double-counting problem of the NoLost field and having got this figure I was able to write the necessary IIf statements to handle the other calculations.

    Many thanks for your help on this though. I have learnt a lot.

    Best wishes for the New Year

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •