Sum in Groups of 12

[LadyDi]

I have a query that lists the total number of parts received by month. I would like to be able to compare those monthly totals with a rolling 12 month total. I've been able to get the rolling 12 month total, but I can only get it to work for one 12 month period. Is there a way to get a 12 month total for each month? In other words for April, I would like a 12 month total of May 2013 through April 2014. For May, I would like a 12 month total of June 2013 through May 2014. Is that possible? If so, how would you go about doing it? I have a field in my table titled REL_MONTH that shows a negative number for how many months ago something is (i.e. 00 = May, -1 = April, -2 = March).

Any assistance you can provide would be greatly appreciated.

 

[plog]

I've seen some of your other posts and think you have the wrong structure to easily accomplish this. The way to do it is using a DSum and comparing date fields to determine which records fall within 12 months of the current record. However, because you have seperate fields for Month and Date values, instead of having them together in a Date field, its going to be more difficult for you.

This is how you would do it, if you had a date field to determine when your value occured. Let's use this table as an example for your data:

YourTableNameHere
ReportDate, ReportValue
1/1/2013, 44
2/1/2013, 51
3/1/2013, 77
...
12/1/2013, 92
1/1/2014, 101


To get the data you want you would use this SQL:

SELECT MONTH(YourTableNameHere.ReportDate) As ReportMonth, YEAR(YourTableNameHere.ReportValue) AS ReportYear, MAX(DSum("[ReportValue]","YourTableNameHere","([ReportDate]+360)>=#" & [ReportDate] & "# AND [ReportDate]<=#" & [ReportDate] & "#")) AS Running12MonthTotal
FROM YourTableNameHere
GROUP BY MONTH(YourTableNameHere.ReportDate), YEAR(YourTableNameHere.ReportValue);

The key though is having the date value as a date.

 

[LadyDi]

That formula works great. Thank you for your help.