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

Running Sum SQL Query Example

Open Email Using SendObject Access Download

User Inactivity Logout VBA Code

Disable Shift Key Demo

Send Email Microsoft Access Tutorial Download (Advanced)

Sequential Counter in Query

Multi Select List Box Query Parameters

Row Level Data Security

Programming MS Access Security Alternative

Single User Inventory Calculations Download

How To Perform Multi-User Inventory Calculations

Make Dependent Combo Box Code MS Access Tutorial

Union Query Example (Simple)

Union Query (Advanced) Access Tutorial

How To Fill Fields From Combo Box

Use Global Variables as Parameters

Continuous Form Dependent Combo Box

How To Program Continuous-Continuous Master/Detail Forms

Access Bar Chart / Bar Graph

Programming Crosstab Query Example

TransferText & OutputTo Microsoft Access

DoCmd.OpenForm & OpenArgs VBA Example

Running Sum Query Method

Choose Command Dynamic SQL Order By

Access Conditional Format

Access Report Banding

Popup Form Control Method

Running Sum SQL Query Example

Running sum query:  I never knew how  to do a running sum query in Microsoft Access.  I believe SQL server and Oracle have added tools for this type of query.  However, it turned out to be pretty simple to create the running sum when combined with global variables.  I'm sure you'll enjoy learning this new technique.

Are you just starting out on your quest for a small business database? Need some advice or someone to bounce ideas off of? We are happy to chat with you to discuss options to your situation.

Are you looking for an MS Access template database? We have many for sale in our Access Template and Examples Software section.

This running sum query method involves initializing global variables through a module during form opening.  You can also do this in an autoexec if you don't want a form.

Then another function in the module simply adds a value to the global sum variable each time it is called.  The function will continuously increase the running sum global variable until the initialization routine is run.

You can also do calculations within the call to the function.  In this example we calculate the total price based on the item price and the quantity ordered.

Below is a snap shot of the resulting recordset:

Access running sum sql query example

Here is the globals module code containing the global variable declaration, the initialization of the running sum variable, and the function which continuously adds to create the running value.

Option Compare Database

Global GBL_Sum As Double
Public Function Init_Globals()
GBL_Sum = 0
End Function

Public Function global_add(ivalue) As Double
GBL_Sum = GBL_Sum + ivalue
global_add = GBL_Sum
End Function

Here is the link to download the Access running sum calculation method.

Below is the SQL Query code for the running sum calculation:

SELECT M_Inv_Out_Items.Inv_Out_Item_ID, M_Inv_Out_Items.Inv_Out_ID, M_Inv_Out_Items.Barcode,
M_Inv_Out_Items.Qty_Out, M_Inv_Out_Items.Item_Price,
global_add([item_price]*[qty_out]) AS [Running Sum]
INTO T_Query_Results
FROM M_Inv_Out_Items;

You'll notice some things in the example that seem more complex than they should be - we agree.  We had to make the sum query write the results to a little table.  Because, if you had the record source of the form set directly to the query then an odd thing happened - if you moved to another program for an instance and when back to viewing the database the form would automatically refresh and all the sum numbers would be larger than they should be because the global variable initialization routine wasn't run.

We think that you could use the  query directly as the record source for a report since the report won't requery itself and is basically locked with the original values.

Try create a little report based directly off of the query and you'll likely see that it will likely work that way.

Also see our visual basic recordset method for creating running sums for forms and reports.

A Blue Claw Software Design Template:

Prescription Assistance Plan Admin Template

A Blue Claw Database Design Article:

ODBC Links To Other DB Systems

Blue Claw Database Design Downloadable Tutorial:
Use Global Variables as Parameters  

Here is a randomly selected programming tutorial:

DoCmd FindNext

Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016
Updated On 2/18/2018 2:55:35 AM

Contact Information

Copyright 2000-2018 Blue Claw Database Design

MS Access 2013, Access 2010 & Microsoft Office 2007