Creating a cumulative running sum from input table, part II

[Bouldergirl]

Hello,

I have an input table like the following:
ZONE DOY GDD
1 1 2.3
1 2 4
1 3 7

2 1 2.8
2 2 6
2 3 4.7

I want to calculate a running sum of the field "GDD" for each "DOY" as a function of "ZONE" (i.e., the running sum re-starts from zero at the beginning of each "ZONE"):

ZONE DOY sum_GDD
1 1 2.3
1 2 6.3
1 3 13.3

2 1 2.8
2 2 8.8
2 3 13.5


In an earlier post, I learned to use the "RunningSum" property to sum, record by record, each line in my table (after sorting by "DOY", as order matters in this calculation). But now I have sets of zones, and so in effect, I want to tally up running sums of my field "GDD" grouped into zones.

The code I have so far is:

Option Compare Database

Function Calc_cum_GDD()
Dim db As Database
Dim tdfNew As TableDef
Dim rin, rout, GDD_test(10000)
Dim zone As Variant
Dim GDD
'Dim zone(10000) As Integer
Dim GDD_cum As Single
ofilename = "LB_runningsum_GDD_Jan_Augzone"
Set db = CurrentDb()
For i = 0 To db.TableDefs.Count - 1 ' Delete table
If db.TableDefs(i).Name = ofilename Then
DoCmd.DeleteObject A_TABLE, ofilename
Exit For
End If
Next
Set tdfNew = db.CreateTableDef(ofilename)
With tdfNew
.Fields.Append .CreateField("zone", dbText)
.Fields.Append .CreateField("doy", dbInteger)
.Fields.Append .CreateField("month", dbByte)
.Fields.Append .CreateField("day", dbInteger)
.Fields.Append .CreateField("GDD", dbSingle)
.Fields.Append .CreateField("Tm", dbSingle)
.Fields.Append .CreateField("cum_GDD", dbSingle)
db.TableDefs.Append tdfNew
End With
Set rinwd = db.OpenRecordset("GDD_LB08312010", dbOpenDynaset)

Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)

rinwd.Sort = "[zone],[doy]"

rinwd.MoveFirst
Do Until rinwd.EOF
zone = rinwd.zone

For Each zone In rinwd.zone
RunningSum = RunningSum + rinwd("GDD_test")

cilf = RunningSum

rout.AddNew
rout![doy] = rinwd.zone
rout![doy] = rinwd.doy
rout![month] = rinwd.month
rout![day] = rinwd.day
rout![GDD] = rinwd.GDD_test
rout![Tm] = rinwd.Tm
rout![cum_GDD] = cilf
rout.Update

rinwd.MoveNext
Next zone
Loop

rinwd.Close: rout.Close
End Function


But I get an error message "Operation is not supported for this type of object" which seems to relate to the line "For Each zone In rinwd.zone"

I guess I need to define how to group my RunningSum records, but I am stuck. Any help is appreciated!

Thanks,
-Tiffany

 

[pbaldy]

Do you really want a new table with the info? That's unusual; normally the running sum is just calculated on the fly in a query with a DSum() or subquery. Even simpler on a report using the Running Sum property of a textbox.

To continue with your method, you'd get rid of the loop and just reset your running sum variable when the zone changes (using another variable to keep track of the zone).