Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003

    Unanswered: Moving month sum

    This forum has helped with some "problems" I have and I am most grateful, but the following has me stumped and I'm getting frustrated. I'm hoping some one can point me in the correct direction...

    Have a table with essentially three columns;
    Autonumber, Date (as mmmm yyyy), MonthValue

    I'm running a report which is printing out twelve months, i.e. January 02 -January 03. The report actually gives; Date (mmmm yyyy), MonthValue, and RunningYrTotal. The "RunningYrTotal value is the total of twelves months to that month. So the May row would have the May MonthValue, but then have RunningYrTotal be the sum of all twelve MonthValues for the year from May 02 - May 03. The next row, June, would have the MonthValue of June and then the RunningYRTotal be the sum of all June 02 - June 03 MonthValues. I busted my knuckles in a Query for a few days now but I just can't seem to get this to work correctly. Most times than naught I end up with a running sum that just totals the report's twelve months. Not sure about sub-queries is the answer and I'm not sure how to kick one off.

    Thanks in advance,

  2. #2
    Join Date
    May 2002
    The only approach I can think of would use DSUM, but its going to be kinda painfull.

    I'm going to assume that you have store the month as a Date/Time value and the actual value is 1-May-2003 for May-2003, If not and this is text, you will need to make some ammendments

    The result would be something like (at qualative level )

    SELECT [Date] , DSUM("[MonthValue]","tblThisTable","([Date] <= #"&[Date] & Format([Date],"dd-mmm-yyyy") & "#) AND ([Date] > #" & Format([Date]-365,"dd-mmm-yyyy") & "#)") AS RunningTotal
    FROM tblThisTable

    Now, I'm sure there should be some syntax troble there and I wouldn't imagine it will run too fast, but have a go and let me know if it worked.

Posting Permissions

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