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];
|
| 