Creating a Running Sum

[tj007]

Hope this can be done
Is there a way to create a Running Sum for a form in Access? I have a field name DIST_STOP which show incremental distances for bus stops. I have a sequential ID field LOC_ID which is unique for each record. Microsoft has advised to do this but I get all zeros in the running sum field.

Open the sample database, Northwind.mdb.
Create the following new query based on the Products table. Query: qryGrpRunSum
-------------------
Type: Select Query

Field: CategoryID
Table: Products
Sort: Ascending

Field: UnitsInStock
Table: Products

In the Field row of the third column in the query design grid, type the following expression:RunSum: fncRunSum([CategoryID], [UnitsInStock])

Save the query as qryGrpRunSum, and then close the query.
Create a new module, and then type or paste the following code:Option Compare Database
Option Explicit

Function fncRunSum(lngCatID As Long, lngUnits As Long) As Long
'Variables that retain their values.
Static lngID As Long
Static lngAmt As Long

If lngID <> lngCatID Then
'If the current ID does not match the last ID, then (re)initialize.
lngID = lngCatID
lngAmt = lngUnits
Else
'If the current ID matches the last, keep a running sum for the ID.
lngAmt = lngAmt + lngUnits
End If

'Pass the running sum back to the query.
fncRunSum = lngAmt
End Function

 

[jjlogan]

Have you considered creating the running sum on a report of the trip. Running sum property could be set to Yes for the incremental distance field on the report; i.e. put the incremental distance field on the report twice, once to show the incremental distance between point A and B, and then the 2nd instance of the field will have its property Running Sum set to Yes.

Generally (don't know your specifics) the tables don't need to store data that can be generated from other fields, e.g. running totals. This data can be generated in a query or on the report itself. It may be possible on a form as well to have running sum - if the form tracks from record to record like a report does - I just have only used running sums on reports.
Jeff

 

[tj007]

No I have not. I will try it now. I will produce it in access and crystal reports. Thanks Jeff