Home  Fees/Services  Access 2010 Template  Access Tutorial  Access Download  Articles  Search  Contact  Privacy  Links

Calculate Running Sum Tutorial
Calculate Running Sum Query Example






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
Concatenate 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
Recordset Filters
Reference Form Field
Select Case
Access Transactions


Visual Basic Function Examples


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.



Blue Claw Database Design is your premier source of Microsoft Access Programming and database support.

With our new partnership with IT Impact we have extensive programming support in SQL/Server database, MS Access software, Power BI applications, .Net web development, PHP, Excel & Outlook customization.


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.










Blue Claw Database Design Downloadable Tutorial:
Make Dependent Combo Box Code MS Access  Tutorial Download  








A Blue Claw Software Design Template:

Interior Design Project Project Management
 










A Blue Claw Database Design Article:

Repair Corrupted Access Table
 

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


Contact Information

Copyright 2000-2017 Blue Claw Database Design

Microsoft Access 2007, 2010, 2013 & 2016