Running Sum in MS-Access Query ~ LEARN MS-ACCESS TIPS AND TRICKS

Monday, November 11, 2019

Running Sum in MS-Access Query

Introduction.

We are familiar with creating Auto-Numbers in Query Column, through an earlier Function created with the Name QrySeq(), published on this Website with the Post Title: auto-numbering Query Column, during January 2010. Hope you come across that Post, if not you may visit the Page by following the above link.

The RunningSum() Function is written somewhat on similar logic, with a few parameters matching the QrySeq() Function.

Before going into details let us take a look at some sample images, before and after a run of the new Function in a test Query Column.

A small Table with two Fields: Table_Units and with few records.

The SELECT Query: RunningSumQ1 record set in datasheet view, with summary values in a separate column, with the column name RunningSum, from where the RunningSum() Function is called from.

The SQL Code of RunningSumQ1 Query.

SELECT Table_Units.ID, Table_Units.Units, RunningSum([ID],"ID","Units","RunningSumQ1") AS RunningSum
FROM Table_Units;

A Report Designed using RunningSumQ1:

The Query Preparation Note.

Before diving deep into the VBA Code I want you to check the above sample data, to draw your attention to an important point while preparing the data for the RunningSum() Function.

A unique ID Field, like Primary Key, is required in the Query, with either Numeric or String Data, and strictly no duplicates in them.
If this is not readily available in the Source-Data, you may join (concatenate) two or more field values together, to create unique values in a separate column, as a Key Field in the Query.
If this method is followed, then create a Test Query similar to the sample one given below, using the first Query as the source, to find out whether any duplicates still exist in the Source Query or not.

Sample ID Field Record-Count Test Query:

SELECT RunningSumQ2.ID2, Count(RunningSumQ2.ID2) AS CountOfID2
FROM RunningSumQ2
GROUP BY RunningSumQ2.ID2;

The CountOfID2 Column result should, like the sample Image given below, with all Count values showing as one.

When all the ID Field values are unique then the CountOfID2 Column will have the value 1 in all records. Greater than one in any record means that those records have duplicate key values and need to join some other field to eliminate duplicates.
Once you are sure that all records have unique ID values then you may add other required fields in the first Query for the purpose you plan to use, like Form or Report Source Query.
Once you are ready with the Query data, then it is time to add the function in a new Column in the Query, like Summary: RunningSum([ID], ”ID”, ”Units”, ”MyQuery”).

The RunningSum() Function VBA Code.

Option Compare Database
Option Explicit

Dim D As Object

Public Function RunningSum(ByVal IKey As Variant, ByVal KeyFldName As String, ByVal SumFldName As String, ByVal QryName As String) As Double
'-----------------------------------------------------------
 'Function: RunningSum()
 'Purpose : Creates Running-Sum Value of a Field.
 'The Query can be used as Source for other Processing needs.
 '-----------------------------------------------------------
 'Author  : a.p.r. pillai
 'Date    : November 2019
 'Rights  : All Rights Reserved by www.msaccesstips.com
 '-----------------------------------------------------------
 'Parameter List, in the Order of it's placement
 '1. Key Value Data Field
 '2. Key-Field Name in String Format
 '3. Field-Name for Calculating Running Sum in String Format
 '4  Query-Name in String Format
 '-----------------------------------------------------------
 'Remarks: The Key-Value Field should have Unique Numeric or
 'String Values.
 '-----------------------------------------------------------
Static K As Long, X As Double, fld As String, y as Long
Dim p As Variant

On Error GoTo RunningSum_Err

y = DCount(“*”,QryName)

If SumFldName <> fld OR K > y Then
    fld = SumFldName
    Set D = Nothing
    K = 0
    X=0
 End If

K = K + 1
 If K = 1 Then
     Dim DB As Database, rst As Recordset

    Set D = CreateObject("Scripting.Dictionary")
 
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset(QryName, dbOpenDynaset)
     
    While Not rst.EOF And Not rst.BOF
          X = X + rst.Fields(SumFldName).Value
          p = rst.Fields(KeyFldName).Value
          
          D.Add p, X
          
          rst.MoveNext
     Wend
     
     rst.Close
     Set rst = Nothing
     Set DB = Nothing
     
     RunningSum = D(IKey)
 Else
    RunningSum = D(IKey)
 End If

If K = y then
   K = K + 1
End If

RunningSum_Exit:
Exit Function

RunningSum_Err:
MsgBox Err & ":" & Err.Description, vbOKOnly, "RunningSum()"
Resume RunningSum_Exit
End Function

VBA Code Line by Line.

On the Global area of the Standard Module, an Object Variable is declared with the name D.

The function RunningSum() is declared with four parameters.

The Unique Key Field Value.
The Key-Field Name in String format.
The Summary Field Name in String format.
The Query-Name in String format.

The returned value from a function is a Double precision number.

Four Static Variables are declared:

K – is a control variable.
X – to hold the Summary Values, added to it at a record level.
fld – is the control variable to keep the Summary Field Name as a flag to ensure that the function runs for the same Query.

The Static Variables will retain their values during repeated calls of the Function.

Variable p is to hold the IDKey value retrieved from the record. It is declared as a Variant Type to accept either Numeric or String Key Values.

The Working Logic of the Function.

The statement If SumFldName <> fld Then checks whether the Key-Field name passed to the function is different from the last call of the Function. If it is different then it assumes that a different Query is passed to the function.

The Dictionary Object D is erased from memory and other variables are initialized.

In the next step, the K Variable is incremented by one. When K=1 the function’s main task is initiated.

The Database and Recordset Objects are declared.

The D Object variable is instantiated as a new Dictionary Object, with the Object creation statement: Set D = CreateObject(“Scripting.Dictionary”).

By default, the Dictionary Object Reference is not added to the list of Microsoft Access Library Files. If you add it manually then you can declare and instantiate a Dictionary Object, like the Class Object of Access or Collection Object.

Note: If you are not familiar with Dictionary, Class Object, or Collection Object, then we have all the information you need to learn the fundamentals about them, on this Web site. The links are given at the end of this page. You may visit them to learn with sample code and Demo databases, as working models to download.

Adding Dictionary Object Reference File.

To add the Dictionary Object to your Database’s Library Files List does the following:

On the VBA Window, select Tools - - >References… and look for the file: Microsoft Scripting Runtime in the displayed list, and put the check mark to select it.

Once you do this you can declare and instantiate a Dictionary Object as given below.

Dim D As Dictionary
Set D = New Dictionary

If you do this you have an added advantage of displaying its Properties and Methods, when you type a dot (D.) after its Object name, by IntelliSense.

Next, the database object DB is set to the active database and the Query is opened at a record set in the rst object.

Within the While. . .Wend Loop the summary field and the unique key Field values are read from each record. The Summary field value is added to Variable X. The Key value of the record is written as Key-Value of Dictionary Object and the current Value in X is written as Dictionary Object Item, as Key, Item pair.

The Dictionary Object Items are always written in this way. The Item can be a single value, an Array object, or a collection of objects. All of them should have a Unique Key Value to retrieve the Item values later.

The purpose of the Key in Dictionary Object is similar to the function of the Primary Key in a Table. We can retrieve any value Randomly or Sequentially from the Dictionary Object using the Key, like A = D(Key) or A = D.Item(Key).

In this way, the cumulative summary value, at each record level, is added to the Dictionary Object as its Item, with a unique Key. When all the record level processing is complete, the first record summary field value is returned to the function calling record by executing the RunningSum = D(IKey) statement, from the first Dictionary Item. All the above actions are taking place when the control-variable K=1.

Subsequent calls of the function with the Key-Value parameter of each record, retrieve the corresponding summary value of that record from Dictionary Item and return it to the Query Column, that’s how it works.

Some Images of a sample Run done on the Products Table of NorthWind are given below.

Sample Query Run (Key Values are String Type) Data on Form.

SELECT Trim(Str([ID])) & [Product Code] AS ID2, Products.[Product Code], Products.[Product Name], Products.[List Price], RunningSum([ID2],"ID2","[List Price]","RunningSumQ2") AS RunningSum
FROM Products;

The RunningSumQ2 Query is the Record Source of the Form.