Creating a cumulative running sum from input table

[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

[FONT=&quot]Function Calc_cum_GDD()[/FONT]
[FONT=&quot]Dim db As Database[/FONT]
[FONT=&quot]Dim tdfNew As TableDef[/FONT]
[FONT=&quot]Dim rin, rout, GDD_prev, GDD_cur[/FONT]
[FONT=&quot]Dim today As Integer, yesterday As Integer[/FONT]
[FONT=&quot]Dim GDD_cum As Single[/FONT]
[FONT=&quot] ofilename = "LB_cum_GDD_Jan_Aug_b5"[/FONT]
[FONT=&quot] Set db = CurrentDb()[/FONT]
[FONT=&quot] For i = 0 To db.TableDefs.Count - 1 ' Delete table[/FONT]
[FONT=&quot] If db.TableDefs(i).Name = ofilename Then[/FONT]
[FONT=&quot] DoCmd.DeleteObject A_TABLE, ofilename[/FONT]
[FONT=&quot] Exit For[/FONT]
[FONT=&quot] End If[/FONT]
[FONT=&quot] Next[/FONT]
[FONT=&quot] Set tdfNew = db.CreateTableDef(ofilename)[/FONT]
[FONT=&quot] With tdfNew[/FONT]
[FONT=&quot] .Fields.Append .CreateField("doy", dbInteger)[/FONT]
[FONT=&quot] .Fields.Append .CreateField("month", dbByte)[/FONT]
[FONT=&quot] .Fields.Append .CreateField("day", dbInteger)[/FONT]
[FONT=&quot] .Fields.Append .CreateField("GDD", dbSingle)[/FONT]
[FONT=&quot] .Fields.Append .CreateField("cum_GDD", dbSingle)[/FONT]
[FONT=&quot] db.TableDefs.Append tdfNew[/FONT]
[FONT=&quot] End With[/FONT]
[FONT=&quot] Set rin = db.OpenRecordset("GGD_cumulativeLB08312010", dbOpenDynaset)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rin.Sort = "doy"[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rin.MoveFirst[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] GDD_cum = rin.GDD[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] cilf = GDD_cum[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rout.AddNew[/FONT]
[FONT=&quot] rout![doy] = rin.doy[/FONT]
[FONT=&quot] rout![month] = rin.month[/FONT]
[FONT=&quot] rout![day] = rin.day[/FONT]
[FONT=&quot] rout![GDD] = rin.GDD[/FONT]
[FONT=&quot] rout![cum_GDD] = cilf[/FONT]
[FONT=&quot] rout.Update[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rin.MoveNext[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Do Until rin.EOF[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] For i = 0 To 1[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] eilf = rin.doy - 1[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] If rin.doy > 1 Then DLookup(rout.GDD, rout, rout.doy = eilf) = GDD_prev[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] GDD_cur = rin.GDD[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] GDD_cum = GDD_prev + GDD_cur[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] cilf = GDD_cum[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rout.Edit[/FONT]
[FONT=&quot] rout.cum_GDD = cilf[/FONT]
[FONT=&quot] rout.Update[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rin.MoveNext[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] Next i[/FONT]
[FONT=&quot] Loop[/FONT]
[FONT=&quot] [/FONT]
[FONT=&quot] rin.Close: rout.Close[/FONT]
[FONT=&quot]End Function[/FONT]

 

[DJkarl]

I would try a completely different approach using SQL. Here is an article on running totals on sub-totals in a query.

http://www.databasejournal.com/feat...ubtotals-and-Grand-Total-Without-a-Cursor.htm

 

[Bouldergirl]

Thanks DJkarl, I will use that query-based approach if need be... I was actually hoping to embed the running total calculation in the VBA module that I wrote which generates my input table in the first place. But thanks for the link!

 

[DJkarl]

The main reason I suggest using SQL is Access tables do not store records in a sorted order, so putting a running total into a table only works if you are retrieving the data using the same sort you used to add the records.

 

[Bouldergirl]

Dear DJkarl, 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 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