I have a table called MHByMonth, containing monthly values across several years, as follows (dashes used here just to try and format layout)
MonthNumber---2010-11---2011-12---2012-13
1---43---69---44
2---37---100---46
3---34---100---49
4---14---73---75
5---25---73---19
6---11---182---64
7---36---103---132
8---56---98---326
9---31---72---
10---151---100---
11---129---43---
12---115---52---
But I need to output a query showing monthly CUMULATIVE totals such as the following:
1---43---69---44
2---80---169---90
3---114---269---139
4---128---342---214
5---153---415---233
6---164---597---297
7---200---700---429
8---256---798---755
9---287---870---
10---438---970---
11---567---1013---
12---682---1065---
I have downloaded the sample database posted on this thread by mahenkj2 and at least I now have data in what I think should be a usable format i.e. with month numbers used to sequence it.
Based on that database, the query I came up with in my database is
I'm just trying to get the 2010-11 column right for starters. It runs, but the results are
ExistingMonthNo RunningTotal2010-11
1 1999
2 3998
3 5997
4 7996
5 9995
6 11994
7 13993
8 15992
9 17991
10 19990
11 21989
12 23988
i.e. it's adding 1999 each time. I have no idea where it's getting this value from or what I've done wrong. I don't really follow the Running Total approach to be honest. I may be misunderstanding what [MonthNo] and [ExistingMonthNo] are trying to do. My month numbers correspond to the month April to March in a UK fiscal year.
This is something I do each month, currently pasting the results of a query into Excel, but I'm trying to get everything done within Access to speed things up. If I get this working, there will be several other similar queries, so i'd love to get to grips with how it should be done.
Thanks.
MonthNumber---2010-11---2011-12---2012-13
1---43---69---44
2---37---100---46
3---34---100---49
4---14---73---75
5---25---73---19
6---11---182---64
7---36---103---132
8---56---98---326
9---31---72---
10---151---100---
11---129---43---
12---115---52---
But I need to output a query showing monthly CUMULATIVE totals such as the following:
1---43---69---44
2---80---169---90
3---114---269---139
4---128---342---214
5---153---415---233
6---164---597---297
7---200---700---429
8---256---798---755
9---287---870---
10---438---970---
11---567---1013---
12---682---1065---
I have downloaded the sample database posted on this thread by mahenkj2 and at least I now have data in what I think should be a usable format i.e. with month numbers used to sequence it.
Based on that database, the query I came up with in my database is
Code:
SELECT MHByMonth.MonthNumber AS ExistingMonthNo, Nz(DSum("2010-11","MHByMonth","[MonthNumber]<=" & [ExistingMonthNo]),0) AS [RunningTotal2010-11]
FROM MHByMonth;I'm just trying to get the 2010-11 column right for starters. It runs, but the results are
ExistingMonthNo RunningTotal2010-11
1 1999
2 3998
3 5997
4 7996
5 9995
6 11994
7 13993
8 15992
9 17991
10 19990
11 21989
12 23988
i.e. it's adding 1999 each time. I have no idea where it's getting this value from or what I've done wrong. I don't really follow the Running Total approach to be honest. I may be misunderstanding what [MonthNo] and [ExistingMonthNo] are trying to do. My month numbers correspond to the month April to March in a UK fiscal year.
This is something I do each month, currently pasting the results of a query into Excel, but I'm trying to get everything done within Access to speed things up. If I get this working, there will be several other similar queries, so i'd love to get to grips with how it should be done.
Thanks.
