| ||||||||||||
|
How To Open Email Using SendObject Access Example Download Send Email Access Sample Download (Advanced) Programming MS Access Security Alternative Inventory Calculations Down Load ( Single User) How To Perform Inventory Calculations (Multi-User) Make Dependent Combo Box Code Access Database Download How To Fill Fields From Combo Box Use Global Variables as Parameters Download Continuous Form Dependent Combo Box How To Program Continuous-Continuous Master/Detail Forms Download Example for Bar Chart/ Bar Graph on Access Forms Programming Crosstab Query Example TransferText & OutputTo Examples DoCmd.OpenForm & OpenArgs VBA Example |
Running Sum SQL Query ExampleRunning 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,
M_Inv_Out_Items.Barcode, 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. |
|
|
Access Database Examples • Microsoft Access Templates • MS Access Tutorials |