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)
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…

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.