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.

1. A unique ID Field, like Primary Key, is required in the Query, with either Numeric or String Data, and strictly no duplicates in them.
2. 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.
3. 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.



4. 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.
5. 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.
6. 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.

1. The Unique Key Field Value.
2. The Key-Field Name in String format.
3. The Summary Field Name in String format.
4. The Query-Name in String format.

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

Four Static Variables are declared:

1. K – is a control variable.
2. X – to hold the Summary Values, added to it at a record level.
3. 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.

The Sample Run Data of Report.

The RunningSumQ2 Query is the Record Source of the Report.

Download the Demo Database.

Download ClsEmployees2007.zip

Download ClsEmployees2003.zip

CLASS MODULE

1. MS-Access Class Module and VBA
   
2. MS-Access VBA Class Object and Arrays
   
3. MS-Access Base Class and Derived Objects
   
4. VBA-Base Class and Derived Object-2
   
5. Base Class and Derived Object Variants
   
6. MS-Access Recordset and Class Module
   
7. Access Class Module and Wrapper Classes
   
8. Wrapper Class Functionality

COLLECTION OBJECT

1. MS-Access and Collection Object Basics
   
2. MS-Access Class Module and Collection Objects
   
3. Table Records in Collection Object

DICTIONARY OBJECT

1. Dictionary Objects Basics
   
2. Dictionary Object Basics-2
   
3. Sorting Dictionary Object Keys and Items
   
4. Display Records from Dictionary
   
5. Add Class Objects as Dictionary Items
   
6. Update Class Object Dictionary Item

MS-ACCESS EVENT HANDLING

1. Withevents MS-Access Class Module
   
2. Withevents and Defining Your Own Events
   
3. Withevents Combo List Textbox Tab
   
4. Access Form Control Arrays And Event
   
5. Access Form Control Arrays And Event-2
   
6. Access Form Control Arrays And Event-3
   
7. Withevents in Class Module for Sub-Form
   
8. Withevents in Class Module and Data
   
9. Withevents and Access Report Event Sink
   
10. Withevents and Report Line Hiding
    
11. Withevents and Report-line Highlighting
    
12. Withevents Texbox and Command Button
    
13. Withevents Textbox Command Button
    
14. Withevents and All Form Control Types

Read More

AutoNumber with Date and Sequence Number

Introduction

We always make use of the Autonumber feature in tables to create a unique ID field for the table. It is easy to define and always starts the auto-number with 1 and increments by 1 for each record unless you set the New Values property to Random rather than the default value Increment.

What is the solution if we need different sequence numbers for the records that we create each day?

For example, assume we are working on a Hospital Project and patients getting registered in the hospital on a particular day should have a unique Registration Card Number consisting of the current date and a three-digit sequence number in the format: yyyymmdd000. The sequence number must reset to 001 when the date changes.

If the Hospital maintains history records of the patients in physical files, organized by Date, Month, and Year-wise order, it is easy for them to locate any file with the Registration Card Number.

We can generate this number automatically with a Function.  Let us try it with a sample Table and Form.

The Autonum() Function

Before that copy and paste the following Function Code into a Standard VBA Module and save it:

Public Function Autonum(ByVal strField As String, ByVal strTable As String) As String
Dim dmval As String, dt1 As String, dt2 As String, Seq As Integer, dv As String

'get the highest existing value from the table
dmval = Nz(DMax(strField, strTable), 0)

'if returned value is 0 then the table is new and empty
'create autonumber with current date and sequence 001
If Val(dmval) = 0 Then
   dv = Format(Now(), "yyyymmdd") * 1000 + 1
   Autonum = dv
   Exit Function
End If

'format the number as an 11 digit number
dv = Format(dmval, "00000000000")
'take the 3 digit sequence number separately
Seq = Val(Right(dv, 3))
'take the date value separately
dt1 = Left(dv, 8)
'get today's date
dt2 = Format(Now(), "yyyymmdd")
'compare the latest date taken from the table
'with today's date
If dt1 = dt2 Then 'if both dates are same
   Seq = Seq + 1 'increment the sequence number
   'add the sequence number to the date and return
   Autonum = Format(Val(dt1) * 1000 + Seq)
   Exit Function
Else 'the dates are different
   'take today's date and start the sequence with 1
   Autonum = Format(Val(dt2) * 1000 + 1)
End If

End Function

The Sample Table and Form

1. Create a sample table with the following structure, as shown in the image given below:

   

2. The Cardno Field is a text type with 11 characters in length.  Both second and third fields are also text fields with sizes of 10 and 50 characters respectively.

3. Save the Table with the name Patients.

4. Use the Form Wizard to design a Datasheet Form for the Patient's Table and name the Form as frm_Patients.

5. Open the Form in Design View.

6. Click on the CardNo Field to select it.

7. Display the Property Sheet (F4). If you are using Access2007 then you can select CardNo from the Selection Type drop-down list.

8. Select the Data Tab and set the following Property Values as shown below:

   • Enabled = Yes

   • Locked = Yes

9. Access2007 users select Form from the Selection Type drop-down control. Earlier version users click on the top left corner of the Form (in the intersection where a black rectangle is shown) to ensure that the Property Sheet belongs to the Form and not of any other control on the Form.

10. Select the Event Tab on the Property Sheet.

11. Select the Before Insert event property and select Event Procedure from the drop-down list.

12. Click on the Build (. . .) Button to open the VBA Module of the Form.

13. Copy the middle line of the following procedure and paste it in the middle of the empty Form_BeforeInsert() lines of code in the Form module.

    Private Sub Form_BeforeInsert(Cancel As Integer)
      Me![CARDNO] = Autonum("CardNo", "Patients")
    End Sub

14.  Save the Form frm_Patients with the changes made.

15. Open the form in normal view and type Mr., in the Title Field, and type some name in the Patient Name field.  You can see that the first field is filled with the current date in yyyymmdd format and the sequence number 001 as the suffix.

16. Type a few more records.  Since we have locked the CardNo field Users cannot edit this field’s content. A sample image is given below:

Test Run of the Code

1. Now, we will test whether the sequence number resets to 001 or not when the date changes. To do that, close the frm_Patients Form.
2. Open the Patients' Table directly in Datasheet View.
3. Change the 7th and 8th digits from left (the dd digits of the date) to the previous date in all the records that you have entered so far. 
4. For example: if the date displayed is 20120109001 then change it to the previous day like 20120108001.
5. When you have completed changing all the records close the Table.
6. Open the Form frm_Patients in normal view and try adding a few more records on the Form. 

Tip:  If you prefer to test it on different dates in the next few days you may do so rather than changing the dates and trying it now itself.

You can see that the Sequence number at the end of the CardNo resets to 001 with the current date and subsequent records’ last three digits get incremented automatically. 

The user cannot change the CardNo manually because we have set the Locked Property Value of the field to Yes.  Since the Enabled Property Value is also set to Yes the User can select this field and search for a specific CardNo, if needed.

Displaying the Number Segment Separately

If you would like to display the sequence number part separate from the date with a dash, (like 20120109-005) we can do that by changing the Input Mask Property of the field, without affecting how it is recorded on the table.

1. Open the frm_Patients in Design View.
2. Click on the CardNo field to select it.
3. Display the Property Sheet (F4) of the Field.
4. Type 99999999-999;;_ in the Input Mask property. 

   Tip: When you set the input mask this way the dash character between the date and sequence number is never stored in the table, it is used for display purposes only.  But, if you enter a 0 between the two semi-colons like 99999999-999;0;_ then the dash character also will be stored in the CardNo field on the table.  It is better if we don’t do that.

5. Save the Form and open it in a normal view.  Now you can distinguish the date and sequence number very easily.

Finding Patient Record.

Assume that a Patient approaches the registration desk with her Registration Card, the staff member at the desk should search for the patient’s record with the CardNo to retrieve her historical record, to know the location of her personal file, to know which doctor the patient attended last, etc.  You have two search options when the search control is displayed. 

Try the following:

1. Click on the CardNo field to select it.
2. Press Ctrl+F to display the search control (the search control image is given below).

As shown on the image above, you can search for the CardNo without the dash if you remove the checkmark from the search options Search Field as Formatted.  Put the check-mark on when searched with the dash separating the date and sequence number.

Download Demo Database.

DateAndSeq.zip

Technorati Tags: Autonumber, Forms, Property

1. Auto-Numbering in Query Column
2. Product Group Sequence with Auto-Numbers.
3. Preparing Rank List.
4. Auto-Number with Date and Sequence Number.
5. Auto-Number with Date and Sequence Number-2.

Read More

Product Group Sequence with Auto Numbers

Introduction.

How to generate automatic sequence numbers for different Categories of Products in a Form.  See the sample data given below to understand the gravity of the problem more clearly:

CatCod	Category	ProdSeq	Product Description
1	Beverages	1	Coffee
1	Beverages	2	Tea
1	Beverages	3	Pepsi
2	Condiments	1	Aniseed Syrup
2	Condiments	2	Northwoods Cranberry Sauce
2	Condiments	3	Genen Shouyu
2	Condiments	4	Vegie-spread
3	Confections	1	Uncle Bob's Organic Dried Pears
3	Confections	2	Tofu
1	Beverages	4
2	Condiments	5

First, two-column values are Product Category Code and Description respectively.  The third column represents the Category-wise Product Sequence Numbers and the last column value is the Product Description.  The product serial is consecutive numbers. Both Category Code combined with the Product Sequence number forms the Primary Key of the Table. Duplicates in the Product Sequence number are not valid.

The Product file contains several products under each category (1. Beverages, 2. Condiments, etc.), and the products under each group should have their own sequence numbers.  The Product sequence should not end up with duplicate values.

When a new product is added to the file under a particular Category, the Form should generate the next product sequence number automatically.  It is impossible to keep track of the last sequence number used under each product group manually, during data entry.  But we can give that job to MS-Access to do it effectively without mistakes.  A simple VBA routine on the Form can do it for us.

An image of a sample Form for the above data is given below:

The first field is the Category Code (1,2,3 etc.) is a lookup field (Combo box) linked to the Category Table.  The first Column Width is set to 0 on the Property Sheet so that the description of the Category is displayed in the Combobox rather than the numeric value.

During data entry, the user selects a Product Category on the first field in preparation for entering a new Product under that Category.  The next sequence number (the existing Product sequence number + 1) inserted automatically in the second field ProdSeq when the User presses Tab Key to leave out the Category field. The User needs to enter only the Product Description manually.  The ProdSeq field may be kept locked so that it is protected from inadvertent changes.

The following program runs on the Category_LostFocus() Event Procedure and finds the existing highest Product Sequence Number for the selected Category from the Table, calculates the next sequence number, and inserts it in the ProdSeq field:

The Category LostFocus Code.

Private Sub Category_LostFocus()
Dim i, s
If Me.NewRecord Then
     i = Me!Category
     s = Nz(DMax("ProdSeq", "Products", "Category = " & i), 0)
     s = s + 1
     Me![ProdSeq] = s
End If 
End Sub

The program works only when the user attempts to create a New Record.

If the Category Code is Text value, then you need to make a change in the criteria part of the DMax() Function as given below:

s = Nz(DMax("ProdSeq", "Products", "Category = ‘" & i & "’"), 0)

Technorati Tags: Microsoft

• Join Split Array Function
• Memo Field and Data Filtering
• Lively Controls on Form
• Create your own Color Palette
• Colors 24 bits and Binary Conversion

Read More