Summation in a query

alexaronson

Active Member
Joined
Sep 30, 2005
Messages
314
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!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi

The following SQL does what you want :

Code:
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...
 

Forum statistics

Threads
1,136,269
Messages
5,674,743
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top