Prev: Form Background Color
Next: DLookUp Issue
From: Vantastic on 5 Nov 2009 21:07
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,
From: Roger Carlson on 6 Nov 2009 09:08
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


"Vantastic" <vantastic-pe(a)nospam.hotmail.com> wrote in message
news:FD21D5BF-1BD0-4EE6-ACD7-6D89598E31F8(a)microsoft.com...
> 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,


From: Daryl S on 6 Nov 2009 13:52
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


"Vantastic" wrote:

> 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,
From: Vantastic on 9 Nov 2009 02:31
Thanks Daryl, with a little modification that worked well. Much appreciated.

"Daryl S" wrote:

> 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
>
>
> "Vantastic" wrote:
>
> > 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,
 | 
Pages: 1
Prev: Form Background Color
Next: DLookUp Issue