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)
wksp.BeginTrans
Set rst = db.OpenRecordset(“Select * from t_Sub_Pay order by subid,absencedate”)
rst.MoveFirst

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

Else

hold_day_Count = hold_day_Count + rst!Day_Count

End If
rst.Edit
rst!running_sum = hold_day_Count    ‘ calculate runing sum here
rst.Update
rst.MoveNext

Loop
wksp.CommitTrans
rst.Close
set rst = Nothing
wksp.close
exit sub

Err_Sum_Button_Click:

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

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