%dim crumb crumb="Running Sum Query Example" Crumb2="Running Sum SQL Query Example" crumb1="Calculate Sum With VBA Functions & Global Variables" %>
Open Email Using SendObject Access Download
User Inactivity Logout VBA Code
Disable Shift Key Demo
Send Email Microsoft Access Tutorial Download (Advanced)
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 (Advanced) Access Tutorial
How To Fill Fields From Combo Box
<% gnum="71116398907" %>
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
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,
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.
MS Access 2003
<% Response.write "Copyright 2000-" & year(now) & " Blue Claw Database Design" %>
MS Access 2013, Access 2010 & Microsoft Office 2007