Cumulative value

[warrio]

Cumulative values

Is there a way to write a query that looks on values and return the
cumulative sum for each month since a specified date?
For instance:

Month | MyValue| CummulativeSum
01.09 | 2 | 2
02.09 | 1 | 3
03.09 | 1 | 4
04.09 | 1 | 5
05.09 | 1 | 6
06.09 | 1 | 7
07.09 | 1 | 8
08.09 | 1 | 9
09.09 | 1 | 10
10.09 | 1 | 11
11.09 | 1 | 12
12.09 | 1 | 13

Thanks for any good suggestion.

 

[Jeff Boyce]

Why are you limiting yourself to using a query?

Have you looked into using the query to return raw values and a report to
display the "running sum"? (check Access HELP on "running sum" property of
reports).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[warrio]

True, so True!!
Thanks for lifting my head from the screen.

But only to satisfy my curiosity, is it possible to do it that in a simple
sql query?

Thanks a lot!

 

[Bob Barrows]

Well, it's not a "simple" query but yes, you can use a subquery.
Something like this ("Month" is not a good field name - it's a reserved
keyword being the name of a VBA function):

select [Month], Sum(Values) As MyValue,
(Select Sum(Values) from table where [Month] <= o.[Month])
As CumulativeSum
from table As o
group by [Month]

You can also use the DSum domain function if you prefer.

 

[John Spencer]

You would need to restructure the values of month (yy.mm instead of mm.yy) to
get this to work if you have more than one year's worth of data.

SELECT [Month], MyValue
, (SELECT Sum(MyValue)
FROM TheTable as Temp
WHERE Temp.Month <= TheTable.Month
And Temp.Month Between '01.09' and '12.09')
FROM TheTable
WHERE [Month] Between '01.09' and '12.09'

A text comparison would find that 02.08 is between 01.09 and 12.09
And if month is a number value then the same thing is true.

And your records could not be sorted in the proper order if the month value
was a string or number field.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[warrio]

Thanks again for all these hints!
I tried with the running sum, it works, but not the right sorting..
Because my report detail is orderd by month descending... Is there a way to
have the other way?

Thanks

Well, it's not a "simple" query but yes, you can use a subquery.
Something like this ("Month" is not a good field name - it's a reserved
keyword being the name of a VBA function):

select [Month], Sum(Values) As MyValue,
(Select Sum(Values) from table where [Month] <= o.[Month])
As CumulativeSum
from table As o
group by [Month]

You can also use the DSum domain function if you prefer.

True, so True!!
Thanks for lifting my head from the screen.

But only to satisfy my curiosity, is it possible to do it that in a
simple sql query?

Thanks a lot!

 

[warrio]

I'll try to substract from the total (not the running sub) the running sum +
the current value...

Thanks again for all these hints!
I tried with the running sum, it works, but not the right sorting..
Because my report detail is orderd by month descending... Is there a way
to have the other way?

Thanks

Well, it's not a "simple" query but yes, you can use a subquery.
Something like this ("Month" is not a good field name - it's a reserved
keyword being the name of a VBA function):

select [Month], Sum(Values) As MyValue,
(Select Sum(Values) from table where [Month] <= o.[Month])
As CumulativeSum
from table As o
group by [Month]

You can also use the DSum domain function if you prefer.

True, so True!!
Thanks for lifting my head from the screen.

But only to satisfy my curiosity, is it possible to do it that in a
simple sql query?

Thanks a lot!

"Jeff Boyce" <[email protected]> a écrit dans le message de (e-mail address removed)...
Why are you limiting yourself to using a query?

Have you looked into using the query to return raw values and a
report to display the "running sum"? (check Access HELP on "running
sum" property of reports).

Regards

Jeff Boyce
Microsoft Access MVP

Cumulative values

Is there a way to write a query that looks on values and return the
cumulative sum for each month since a specified date?
For instance:

Month | MyValue| CummulativeSum
01.09 | 2 | 2
02.09 | 1 | 3
03.09 | 1 | 4
04.09 | 1 | 5
05.09 | 1 | 6
06.09 | 1 | 7
07.09 | 1 | 8
08.09 | 1 | 9
09.09 | 1 | 10
10.09 | 1 | 11
11.09 | 1 | 12
12.09 | 1 | 13

Thanks for any good suggestion.

 

[warrio]

Thanks again for your help.
Have a good day!
Best regards