Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2007
    Posts
    3

    Unanswered: Help! 12-month rolling sum in Access!?!?

    Please help...I've tried many different methods for something that seems like it should be simple. I would like to create a query that calculates the 12-month rolling sum for each month between the user-specified beginning month and ending month. The table below is what I am trying to accomplish for the query output.

    Here is my most recent SQL...

    PARAMETERS BegDate DateTime, EndDate DateTime;
    SELECT Sum(Alias.Amount) AS SumOfAmount, Alias.UsageDate
    FROM (SELECT tblRolling.UsageDate, tblRolling.Amount FROM tblRolling WHERE tblRolling.UsageDate BETWEEN DateSerial(Year(DateAdd("m",-1,[BegDate]))) AND [EndDate]) AS Alias
    WHERE (((Alias.UsageDate) Between DateAdd("m",-11,[UsageDate]) And [UsageDate]))
    GROUP BY Alias.UsageDate;

    The output for the SQL above just gives me the Amount in both the 'Amount' column and the 'RollingSum' column, instead of the table below (which is what I want!) :-)


    Date Amount RollingSum
    Jan-05 296
    Feb-05 308
    Mar-05 585
    Apr-05 627
    May-05 421
    Jun-05 525
    Jul-05 125
    Aug-05 506
    Sep-05 570
    Oct-05 290
    Nov-05 279
    Dec-05 280 4812
    Jan-06 254 4770
    Feb-06 254 4716
    Mar-06 253 4384
    Apr-06 328 4085
    May-06 275 3939
    Jun-06 281 3695
    Jul-06 498 4068
    Aug-06 269 3831
    Sep-06 187 3448
    Oct-06 393 3551
    Nov-06 559 3831
    Dec-06 202 3753
    Thank you!

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Try this. You'll have to modify it to include your parameters and to format the usage date, but I think it yields the results you want. I assumed the UsageDate is a date/time field.

    Code:
    SELECT MonthlyUsage.EndOfMonth, First(MonthlyUsage.Amount) AS Monthly, Sum(AnnualUsage.Amount) AS Rolling 
    FROM (
       SELECT DateSerial(Year(DateAdd("m",1,tblRolling.UsageDate)),Month(DateAdd("m",1,tblRolling.UsageDate)),1)-1/24/60/60 AS EndOfMonth, tblRolling.Amount 
       FROM tblRolling 
       GROUP BY DateSerial(Year(DateAdd("m",1,tblRolling.UsageDate)),Month(DateAdd("m",1,tblRolling.UsageDate)),1)-1/24/60/60, tblRolling.Amount) AS MonthlyUsage, 
       (
       SELECT DateSerial(Year(DateAdd("m",1,tblRolling.UsageDate)),Month(DateAdd("m",1,tblRolling.UsageDate)),1)-1/24/60/60 AS EndOfMonth, tblRolling.Amount 
       FROM tblRolling 
       GROUP BY DateSerial(Year(DateAdd("m",1,tblRolling.UsageDate)),Month(DateAdd("m",1,tblRolling.UsageDate)),1)-1/24/60/60, tblRolling.Amount) AS AnnualUsage 
    WHERE (((AnnualUsage.EndOfMonth) Between DateAdd("m",-11,[MonthlyUsage].[EndOfMonth]) And [MonthlyUsage].[EndOfMonth]))
    GROUP BY MonthlyUsage.EndOfMonth;

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Is there a reason why you're stuffing report logic into a query?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Apr 2003
    Location
    Alabama, USA
    Posts
    154
    I agree with Teddy in that if there is no real reason to have a running total in a query then a report is where this should be done. If you aren't doing anything with the running total other than viewing it, then there's no reason to do it in a query.

    That being said, it can be done in a query.

    I know your data isn't exactly like this but assuming we had data like this.

    dtDate, lngValue
    1/1/2007, 0.01
    2/1/2007, 0.1
    3/1/2007, 1
    4/1/2007, 10
    5/1/2007, 100
    6/1/2007, 1000
    7/1/2007, 10000
    8/1/2007, 100000
    9/1/2007, 1000000
    10/1/2007, 10000000
    11/1/2007, 100000000
    12/1/2007, 1000000000
    1/1/2008, 0.2
    2/1/2008, 2
    3/1/2008, 20
    4/1/2008, 200
    5/1/2008, 2000
    6/1/2008, 20000

    We can use a subquery statement to create a running total.
    Code:
    SELECT dtDate, lngValue, 
      (SELECT Sum([lngValue]) 
       FROM tblData 
       WHERE 0>=DateDiff("m", tblData_Copy.dtDate, [dtDate]) 
       And DateDiff("m", tblData_Copy.dtDate, [dtDate])>=-11) 
       AS RunningSum
    FROM tblData AS tblData_Copy;
    dtDate, lngValue, RunningSum
    1/1/2007, 0.01, 0.01
    2/1/2007, 0.1, 0.11
    3/1/2007, 1, 1.11
    4/1/2007, 10, 11.11
    5/1/2007, 100, 111.11
    6/1/2007, 1000, 1111.11
    7/1/2007, 10000, 11111.11
    8/1/2007, 100000, 111111.11
    9/1/2007, 1000000, 1111111.11
    10/1/2007, 10000000, 11111111.11
    11/1/2007, 100000000, 111111111.11
    12/1/2007, 1000000000, 1111111111.11
    1/1/2008, 0.2, 1111111111.3
    2/1/2008, 2, 1111111113.2
    3/1/2008, 20, 1111111132.2
    4/1/2008, 200, 1111111322.2
    5/1/2008, 2000, 1111113222.2
    6/1/2008, 20000, 1111132222.2

    The trick is to make sure that the WHERE clause in the subquery is contructed properly to sum only data you want to sum.

    Running Calculations (Total/Sum, Average, Count) in a Query
    http://AccessDB.Info

    You live and learn. At any rate, you live. - Douglas Adams

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •