Results 1 to 9 of 9

20041230, 12:43 #1
 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.

20041230, 12:46 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20041230, 13:45 #3
 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 NoPlantedNoLost. 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

20041230, 13:51 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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?

20041230, 14:56 #5
 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

20041230, 15:19 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: Selecting Which Records to Sum etc. (Access 2003)
The solution will probably involve the DSum function: DSum("Fieldorexpression", "Tableorquery", "Optional wherecondition") will calculate the sum of the values of the field (or expression) for all records from the table (or query) satisfying the wherecondition (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.

20041230, 18:07 #7
 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 semisuccessful 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

20041230, 18:14 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 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.

20041231, 15:14 #9
 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 doublecounting 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