How to calculate a running sum based on an ordered input table? 1

[Bouldergirl]

Hello,
My problem is this: I have an input table (called rin in the following code) with a field (GDD) that I am hoping to create a running, cumulative sum of (in the output table field called “cum_GDD”) and pass these values into an output table (rout). I realize that VBA doesn’t have any conception of the operation it has performed in a previous iteration of a loop, and I’ve tried numerous tactics to overcome this, without success. The closest I’ve come thus far is to attempt to sum the current input table value (GDD) for the current “rin” record with the previous value calculated and passed into “rout”, using the function rout.MovePrevious. Unfortunately, I get the run-time error ‘3201’: No current record.
I guess I don’t completely understand how moving to a previous record works, but my strategy was to move to the previous iteration of the calculated cumulative sum of GDD (cum_GDD) in “rout”, hold this value, and then move to the next (presumably as yet unwritten) “rout” record to place the new sum into “rout”, using rout.MoveNext. I eventually tried to pull the previous “rout.cum_GDD” value out using dLookup and attempting to make the lookup criteria look for the previous record by equating this record to the field by which I sort the records (order matters in the running sum calculation, so I sort the input table by a field called “doy”, and this is about as far as I have gotten).
Does anyone have any suggestions for me? Please use lay-person lingo, as I’m not exactly a seasoned programmer. I don’t know if I could alternately pass the entire input table into an array and work with it that way, but I don’t know how to do that.
Thanks in advance for any and all help!
-Tiffany


Option Compare Database

Function Calc_cum_GDD()
Dim db As Database
Dim tdfNew As TableDef
Dim rin, rout, GDD_prev, GDD_cur
Dim today As Integer, yesterday As Integer
Dim GDD_cum As Single
ofilename = "LB_cum_GDD_Jan_Aug_b5"
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("doy", dbInteger)
.Fields.Append .CreateField("month", dbByte)
.Fields.Append .CreateField("day", dbInteger)
.Fields.Append .CreateField("GDD", dbSingle)
.Fields.Append .CreateField("cum_GDD", dbSingle)
db.TableDefs.Append tdfNew
End With
Set rin = db.OpenRecordset("GGD_cumulativeLB08312010", dbOpenDynaset)

Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)

rin.Sort = "doy"

rin.MoveFirst

GDD_cum = rin.GDD

cilf = GDD_cum

rout.AddNew
rout![doy] = rin.doy
rout![month] = rin.month
rout![day] = rin.day
rout![GDD] = rin.GDD
rout![cum_GDD] = cilf
rout.Update

rin.MoveNext

Do Until rin.EOF

For i = 0 To 1

eilf = rin.doy - 1

If rin.doy > 1 Then DLookup(rout.GDD, rout, rout.doy = eilf) = GDD_prev

GDD_cur = rin.GDD

GDD_cum = GDD_prev + GDD_cur

cilf = GDD_cum

rout.Edit
rout.cum_GDD = cilf
rout.Update

rin.MoveNext

Next i
Loop

rin.Close: rout.Close
End Function

 

[MichaelRed]

with out going into details, a common approach is "Save" aa value between passes with the Keyword "STATIC". See help (aka F1) for a brief discussion.



MichaelRed

 

[MichaelRed]

A Simple usage

Public Function basSeqNum(Optional SeedVal As Long = 0) As Long

    'Michael Red 12/26/2001
    'To Provide "Sequential" Numbering of Records

    'Sample Usage:
    'SeqNum: basSeqNum([NWeeks])
    '   Where "[NWeeks]" is any field in the Table (or Query) being Numbered

    'There are some issues with using this in a select query'
    'but it should "Re-Sequence" a _table_ without problems
    'when included in an UPDATE Query

    Static MySeqNum As Long

    If (SeedVal < 0) Then
        MySeqNum = 0
    End If

    MySeqNum = MySeqNum + 1

    basSeqNum = MySeqNum

End Function

MichaelRed

 

[Bouldergirl]

Dear MichaelRed, Thanks for the advice... I posted this thread on another forum, and someone suggested I use the "RunningSum" property, which, based on the always confusing MS Access documentation, I had ignored as I thought this could only be used for reports. For anyone out there with a similar problem, here's the code I have that now works like a charm!

Option Compare Database

Function Calc_cum_GDD()
Dim db As Database
Dim tdfNew As TableDef
Dim rin, rout, GDD_prev, GDD_cur
Dim today As Integer, yesterday As Integer
Dim GDD_cum As Single
ofilename = "LB_runningsum_GDD_Jan_Aug_b5"
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("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("GGD_cumulativeLB08312010", dbOpenDynaset)

Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)

rinwd.Sort = "doy"

rinwd.MoveFirst


Do Until rinwd.EOF

RunningSum = RunningSum + rinwd("GDD_test")

cilf = RunningSum

rout.AddNew
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

Loop

rinwd.Close: rout.Close
End Function