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.

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:

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.