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.



We have dozens of downloadable Access small business databases for you to learn and examine.

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


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:

Construction Project Software
 










A Blue Claw Database Design Article:

Computer Disaster Recovery Planning
 









Blue Claw Database Design Downloadable Tutorial:
Access Conditional Format  









Here is a randomly selected programming tutorial:

Email Attachment
 


Microsoft Office:
MS Access 2003
Access 2007
Access 2010
Access 2013
Access 2016
Updated On 5/23/2017 9:01:29 PM

Contact Information

Copyright 2000-2017 Blue Claw Database Design

MS Access 2013, Access 2010 & Microsoft Office 2007