Cumulative Sum Query

[Vantastic]

Hi
I need to run a cumulative total in a query based on expenditure per month
so that I can plot two lines on a chart, monthly expenditure and cumulative
expenditure.

Columns would be Month, Value, and then cumulative value... i have had no
success writing a query to do this, yet its a simple function to perform in
excel

Appreciate any input.

TIA,

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "RunningSumInQuery.mdb " which illustrates how to do this.
You can download it for free here:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=279

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Daryl S]

Vantastic -

I will assume you have a table with expenditures in it by date. If they are
already summed by month it would be easier. The trick to doing this with one
query is to add a table with the YearMonths in them. For this code, my
YearMonths table contains a single field called YearMonth, which is a text
value and the primary key. The values look like "2009_01","2009_02", etc.

I assume a table called Expenditures has the fields [ExpenditureDate] and
[Expenditure] in them. This query will show both the monthly expenditures
and the cumulative expenditures, starting the accumulation over each year.


SELECT YearMonths.YearMonth, Sum(IIf(Year([ExpenditureDate]) & "_" &
Format(Month([ExpenditureDate]),"00")=[YearMonth] And
Year([ExpenditureDate])=Val(Left([YearMonth],4)),[Expenditure],0)) AS [Month
Expenditure], Sum(IIf(Year([ExpenditureDate]) & "_" &
Format(Month([ExpenditureDate]),"00")<=[YearMonth] And
Year([ExpenditureDate])=Val(Left([YearMonth],4)),[Expenditure],0)) AS
[Cumulative Expenditure]
FROM Expenditures, YearMonths
GROUP BY YearMonths.YearMonth
ORDER BY YearMonths.YearMonth;

The trick is to sum all expenditures by month when the year/month matches
the YearMonth value in the new table to get the monthly expenditures, and to
sum all expenditures for any YearMonths less than or equal to the current
YearMonth, but only for the current year. For this to work, the YearMonth
format chosen for the new table must sort properly by year and then month.

Hope this helps.

 

[Vantastic]

Thanks Daryl, with a little modification that worked well. Much appreciated.

Vantastic -

I will assume you have a table with expenditures in it by date. If they are
already summed by month it would be easier. The trick to doing this with one
query is to add a table with the YearMonths in them. For this code, my
YearMonths table contains a single field called YearMonth, which is a text
value and the primary key. The values look like "2009_01","2009_02", etc.

I assume a table called Expenditures has the fields [ExpenditureDate] and
[Expenditure] in them. This query will show both the monthly expenditures
and the cumulative expenditures, starting the accumulation over each year.


SELECT YearMonths.YearMonth, Sum(IIf(Year([ExpenditureDate]) & "_" &
Format(Month([ExpenditureDate]),"00")=[YearMonth] And
Year([ExpenditureDate])=Val(Left([YearMonth],4)),[Expenditure],0)) AS [Month
Expenditure], Sum(IIf(Year([ExpenditureDate]) & "_" &
Format(Month([ExpenditureDate]),"00")<=[YearMonth] And
Year([ExpenditureDate])=Val(Left([YearMonth],4)),[Expenditure],0)) AS
[Cumulative Expenditure]
FROM Expenditures, YearMonths
GROUP BY YearMonths.YearMonth
ORDER BY YearMonths.YearMonth;

The trick is to sum all expenditures by month when the year/month matches
the YearMonth value in the new table to get the monthly expenditures, and to
sum all expenditures for any YearMonths less than or equal to the current
YearMonth, but only for the current year. For this to work, the YearMonth
format chosen for the new table must sort properly by year and then month.

Hope this helps.

--
Daryl S

Hi
I need to run a cumulative total in a query based on expenditure per month
so that I can plot two lines on a chart, monthly expenditure and cumulative
expenditure.

Columns would be Month, Value, and then cumulative value... i have had no
success writing a query to do this, yet its a simple function to perform in
excel

Appreciate any input.

TIA,