Running Sum on an input table as a function of group # 1

[Bouldergirl]

Hello,

I have an input table like the following:
ZONE DOY GDD
1 1 2.3
1 2 4
1 3 7
1 4 3.1
2 1 2.8
2 2 6
2 3 4.7
2 4 5

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
1 4 16.4

2 1 2.8
2 2 8.8
2 3 13.5
2 4 18.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

 

[Gruuuu]

Right before that, you are assigning zone to rinwd.zone

I'm not terribly savvy with recordsets in Access VBA, but if rinwd.zone is a collection, you won't be able to step through it like that.

Otherwise, if rinwd.zone isn't a collection, you won't be able to do what you're trying to do.

Why don't you save the previous value of zone and compare the new value of zone to it? total the cumulative sum only if the current zone is the same as the last one.

 

[Bouldergirl]

Hi Gruuuu, thanks for your reply.... Conceptually, I know what you are talking about, but pragmatically, I don't know how to save the value and compare it to the next one....

I do think that rinwd.zone is a collection, since collections are objects of the same type (and I guess my column called "zone" is an object?)

I tried to simply declare zone as a collection (Dim zone As Collection) and then I added the following code (starting after my sort statement):

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

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

For Each zone In rinwd
Do
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
Loop
Next zone
Loop
rinwd.Close: rout.Close


But I get the error "Operation is not supported for this type of object" in the "For Each zone In rinwd" line.

Any ideas on how I might proceed? Thanks!
-Tiffany

 

[Gruuuu]

Well you can't use a collection to step through a collection. You need to define an object type that matches the object types of the collection you're stepping through. Unless it is a collection of collections? But then the step through collection has to be of the same type as the... ah geez I've confused myself now.

Anyway, think of it like a table. A table is a collection of rows. You're trying to step through your 'table' using the same table (by assigning it to a variable).

I hope that made sense

Anyway,
maybe something like this:

[tt]
Option Compare Database
[red]Option Explicit[/red]

Function Calc_cum_GDD()
Dim db As Database
Dim tdfNew As TableDef
Dim rin [green](what is rin? what data type? where is it used?)[/green], rout[green](what data type?)[/green], GDD_test(10000)[green](what data type?)[/green]
Dim prvZone as Single
Dim GDD[green](what data type?)[/green]
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
[red]prvZone = 0
RunningSum = 0[/red]
Set rinwd = db.OpenRecordset("GDD_LB08312010", dbOpenDynaset)

Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)

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

rinwd.MoveFirst
Do Until rinwd.EOF



[red]If prvZone = rinwd.zone Then
RunningSum = RunningSum + rinwd.zone
Else
RunningSum = rinwd.zone
End If[/red]

cilf = RunningSum

rout.AddNew
rout![zone] = 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] = [red]RunningSum[/red]
rout.Update

[red]prvZone = rinwd.zone[/red]
rinwd.MoveNext
Loop

rinwd.Close: rout.Close
End Function
[/tt]

I added in some notes and a few other things.

It's always a fantastic idea to Dimensionalize (Dim) your variables with the appropriate data type.

This hasn't been tested. I sincerely hope it works for you, or you can figure out what I was going for.

 

[Bouldergirl]

Dear Gruuuu, you are a Guru! Thanks so much. With just a few minor tweaks, I made your suggestion work perfectly!

For those out there who may be looking to do a similar thing, here's the final code that works for me:

Option Compare Database
Option Explicit
Function Calc_cum_GDD()
Dim db As Database
Dim tdfNew As TableDef
Dim rinwd, rout, ofilename, i
Dim prvZone As Integer
Dim GDD As Single
Dim GDD_cum As Single
Dim RunningSum 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("ID", dbInteger)
.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

prvZone = 1
RunningSum = 0

Set rinwd = db.OpenRecordset("GDD_LB08312010", dbOpenDynaset)

Set rout = db.OpenRecordset(ofilename, dbOpenDynaset)

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

rinwd.MoveFirst
Do Until rinwd.EOF

If prvZone = rinwd.ID Then
RunningSum = RunningSum + rinwd("GDD_test")

Else
RunningSum = rinwd.GDD_test
End If

rout.AddNew
rout![ID] = rinwd.ID
rout![doy] = rinwd.doy
rout![month] = rinwd.month
rout![day] = rinwd.day
rout![GDD] = rinwd.GDD_test
rout![Tm] = rinwd.Tm
rout![cum_GDD] = RunningSum
rout.Update
prvZone = rinwd.ID
rinwd.MoveNext

Loop
rinwd.Close: rout.Close
End Function

 

[kendue]

It seems that you could get the results you desire without ANY VBA code by simply using a fourth column in your spreadsheet named "Sum GDD".

Col A B C D
Zone DOY GDD Sum GDD

In Column D, type the formula "=IF(A2=A1, SUM(D1+c2),C2)" and copy this formula the length of your table.