Calculate YTD and Rolling-12 Month Totals

[McArthurGDM]

I have four fields of information



Year | Month | Type | SubType | Value




I want to group first based on Type and then on the SubType and do a YTD and Rolling-12 calculation based on the Year and Month provided. This will undoubtedly be 2 separate queries.

Also, IF POSSIBLE:


If there ARE NOT 12 months to "roll" on for a particular month, to show a "-" or something to signify that there are not 12-months to roll on.


I'm really just not skilled enough to write this query. If someone can help me write it, I would be unbelievably appreciative.

[McArthurGDM]

I figured out how to do the YTD calculation in it's own query.

Month_Year is just a field of standard dates of the form 1/2/2015 or whatever.

SELECT b.OE, b.ValueID, b.MetricID, b.Month_Year, (select sum(a.Value) 
        from ValueT as a 
        where a.Months <= b.Months AND a.Years = b.Years and
              a.MetricID = b.MetricID and a.OE = b.OE
       ) AS ValueYTD INTO new_table
FROM ValueT AS b
GROUP BY b.OE, b.ValueID, b.MetricID, b.Month_Year, b.Months, b.Years
ORDER BY b.OE, b.MetricID, b.Month_Year;

[CJ_London]

duplicate post here

https://www.accessforums.net/access/...tml#post270859