Summation in a query

[alexaronson]

Hello, I am a newbie when it comes to Access and could use a little help.

I want to have a query that displays the three columns: "Date", "Count", "RunTotal"

In the Date column is a list of dates numerically in order.

In the Count column is a summation or a lack of a better word the number of occurrencein a day.

Where I need help with is the RunTotal column. I want to have a running total of values based on the Count Column.

For instance:
Date Count RunTotal
9/22/2006 1 1
9/23/2006 0 1
9/24/2006 3 4
9/25/2006 3 7
9/26/2006 0 7
9/27/2006 0 7
9/28/2006 5 12
9/29/2006 0 12
9/30/2006 2 14



Any help will be greatly appreciative!

 

[Andrew Fergus]

Hi

The following SQL does what you want :

SELECT MyTableAlias.Date, 
MyTableAlias.Count, 
(Select Sum([Count]) FROM MyTable Where [MyTable].Date <= [MyTableAlias].Date) AS RunSum 
FROM MyTable AS MyTableAlias 
ORDER BY MyTableAlias.Date;

Copy and paste this SQL into a new query (create the query and then click View > SQL View) but change the table name of 'MyTable' to your actual table name. Leave the 'MyTableAlias' part as is. Be aware that these sorts of queries can be slow where there are many many records involved.

HTH, Andrew

P.S. This should probably be in the Access forum...