Running total in query

[John1Chr]

Is it possible to do a running total in a query?

 

[dhookom]

This is not possible in any way. Don't look at the first three postings in the FAQs of this forum ;-)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[dhookom]

I should have added this on also faq701-5268.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[John1Chr]

The search feature wasn't working for me this morning. I got this message, "Search functionality is currently undergoing routine maintenance and will be back online shortly."

Stupid me, I didn't even think of looking in the FAQ's. Maybe, I'm drinking decaffinated?

It looks like ACEMAN1 wrote a routine.

 

[dhookom]

Stick with the caffiene

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[John1Chr]

I got the running total to work by creating the table : tbl_expenditures with these fields: input number (autonumber), date, description, and amount.

I ran a query off tbl_expenditures. (qry_expenditures_tbl) This is the SQL: SELECT tbl_expenditures.[input nbr], tbl_expenditures.date, tbl_expenditures.description, tbl_expenditures.amount
FROM tbl_expenditures;

I ran a query off of the other query:

SELECT qry_expenditures_tbl.date, qry_expenditures_tbl.description, qry_expenditures_tbl.amount, (DSum("[amount]","qry_expenditures_tbl","[input nbr]<=" & [input nbr])) AS [Running Balance]
FROM qry_expenditures_tbl;


The “running total” works fine in the query for my purposes.

 

[jozino01]

check this article:

http://support.microsoft.com/kb/290136

How to create a running totals query in Microsoft Access

 

[jozino01]

and here is another way:


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

http://www.fabalou.com/Access/Queries/RunningTotalQuery.asp