Calculate Running Sum Recordset Operation


Calculate Running Sum Recordset Example

Here is a fairly simple way to calculate a running sum using a DAO Recordset operation. To begin, we have created a temporary table with Absence Dates, and Substitute Teach ID (SubID) – this table was created via a previous query not shown in this example.Our goal is to determine running sum of substitute records based on absence date (teacher was absent and therefore a sub worked in this day).

In the example we used rst!day_count rather than just counting records because the sub can work either 1/2 days or full days (1).  Here is the VBA code:

Private Sub Sum_Button_Click()
On Error GoTo Err_Sum_Button_Click

Download new method for calculating running sums directly in queries using global variables

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim hold_subid As Long
Dim hold_day_Count As Long
Dim sqltext As String
Dim wksp As DAO.Workspace
Set db = CurrentDb

‘calculate running sum of days worked.

Set wksp = DBEngine.Workspaces(0)
Set rst = db.OpenRecordset(“Select * from t_Sub_Pay order by subid,absencedate”)

hold_subid = rst!SubID
hold_day_Count = 0

Do While Not rst.EOF

If hold_subid <> rst!SubID Then

‘ note that we reset the counter when a new sub teacher is encountered
‘ new sub teacher
hold_day_Count = rst!Day_Count
hold_subid = rst!SubID


hold_day_Count = hold_day_Count + rst!Day_Count

End If
rst!running_sum = hold_day_Count    ‘ calculate runing sum here

set rst = Nothing
exit sub


wksp.rollback ‘ cancel transactions if there is an error
set rst=nothing
resume quit_it

End Sub

Note that this example also uses Transaction Processing – in this case the time to do the running sum processing was reduced by about 75%.

More examples to come soon…

See some additional queries in Access Visual Basic.

Microsoft Office:
MS Access 2000 Through 2016 and Office 365 & SharePoint

Microsoft Access 2007, 2010, 2013 & 2016


Microsoft Office VBA, MS Access 2003, 2007, 2010, 2013, 2016