Microsoft Access Office VB VBA Help and Examples
           
             

Running Totals in a Query

You have a list of sums and want to create a running sum inside a query of those sums. You need to have a unique field in the underlying table. Also you can create an autonumber in the same manner as an autonumber field in a table. Please note you must have a sortable unique key to be able to do this.

Here's how to create a Running Sum. Create a new query without any tables in it. Switch to the SQL view of the query and paste the following code in.

SELECT [tbl_Orig].[UniqueKey], (SELECT Sum([tbl_Orig].[FieldToSum]) AS Total
FROM [tbl_Orig]
WHERE ((([tbl_Orig].[UniqueKey])<=[tbl_Alias].[UniqueKey]));) AS Total
FROM [tbl_Orig] AS [tbl_Alias];

Before going back to the normal view, change all occurences of 'tbl_Orig' with the name of your table, all occurences of 'UniqueKey' with the name of your unique key in the table and the one occurence of field to sum with the field to be totalled.

Go back to design mode and add any extra fields from your original table you desire. Then run the query

Points to note. Your original table must be sorted by the Unique Key ascending, if it is not or you cannot then create a new query with all the fields in and sort by the Unique Key and then base this query on the sorted query.

To create an auto number repeat the above process but use the following SQL instead.

SELECT [tbl_Orig].[UniqueKey], (SELECT Count([tbl_Orig].[FieldToCount]) AS AutoNum
FROM [tbl_Orig]
WHERE ((([tbl_Orig].[UniqueKey])<=[tbl_Alias].[UniqueKey]));) AS Total
FROM [tbl_Orig] AS [tbl_Alias];
 
HOME   SEARCH SITE   PRIVACY POLICY   CONTACT
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.