473,327 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

How to calculate running sum in VBA based on an ordered input table

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
Mar 30 '11 #1

✓ answered by Rabbit

Expand|Select|Wrap|Line Numbers
  1. Do Until recordsetVariable.EOF
  2.    runningSum = runningSum + recordsetVariable("fieldname")
  3.    ' Do stuff with runningSum
  4. Loop

2 5089
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. Do Until recordsetVariable.EOF
  2.    runningSum = runningSum + recordsetVariable("fieldname")
  3.    ' Do stuff with runningSum
  4. Loop
Mar 30 '11 #2
By golly, it worked! Based on the VBA documentation in Access, I thought this function could only be used for Reports.... thanks Rabbit!!!!! -Tiffany
Mar 30 '11 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: jochen scheire | last post by:
Is there a way I can calculate a field in a form based on another field in the same form. When clicking submit, both values should be posted to the next page. I want to be able to type in a value...
0
by: Leonardo Gangemi | last post by:
How to align right a table based on another table created dinamically? Leonardo
1
by: gblakewood | last post by:
Simple question (I think)... I have a form based on a table and I want it to open with it sorted on Last Name ascending. I set the order by property for the form to: tblPersonnel. ASC but it...
9
by: FunkHouse9 | last post by:
I am trying to set up a page that calculates a total ad price based on a word count value. I have the word count displaying properly in op3, but just can't get the price even close to working right....
2
by: hafner | last post by:
Hello all, I have an extremely simple query I'm running on a linked table. However, when I run it (primarily, I'm exporting to a .txt file, but the behavior persists even if I run the query), it...
1
by: babyjek | last post by:
SELECT ProjectID, ProjectName, ProjectDescription, SequenceNumber, LastUpdateDate FROM Project WHERE ProjectID = @ProjectID; (The Microsoft Jet database engine cannot find the input table or...
0
by: rkandas | last post by:
Order by based on input Select col1, col2, col3 from table 1 order by col1 <ASC/DESC>, col2 <ASC/DESC>, col3 <ASC/DESC> The sort order for col1, col2, col3 are parameters to the program. Can...
6
by: warpcon | last post by:
Im trying to build a database starting with one of the templates that come with access. I took out the employee part in all the tables and forms. Now when I pull up a workorder and then try to view...
4
by: Manikrag | last post by:
Hi Team, Is it possible to sort select query based on input string? I am looking for somthing like: select TOP 20 PREFERRED_NAME from FRS_TABLE where Lower(PREFERRED_NAME) like...
0
by: Aaron Lawrence | last post by:
The company I work for currently uses a number of excel workbooks for asset tracking, job reports, ect. This system is very clumsy and requires a bunch of redundant data entry. I started a pet...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.