Calculate Running Sum Recordset Example

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…

Visual Basic Tutorials:

Access-Google Earth Age Calculation Change To Proper Case Email via Gmail #1
Send Gmail Email #2 Inactivity Logout Code Outlook Email Read Email Access
Email Attachment Send Outlook Email Running Sum Denormalize Records
Stock Quotes CreateQueryDef Find Database Path Detail-Master Update
Data Field Validation Field Value New-Old FindFirst Access Version
Global Variable Parameter Global Variables Active Labels Files List Box
Mail Merge Quick Sort Select Case Access Transactions

See some additional queries in Access Visual Basic.