Create a rolling 8 months total based on current records date

[#1]

Dear Community,

Hope you can provide some guidance on how to solve this problem.

My table is strutured as follows

MyDate               Cost of Sales     Rolling 8 months cost of sales

1/15/2012        100                                        ??

2/15/2012        200                                        ??

3/15/2013        300                                        ??

Where ?? is a sum of the current plus previous 8 months Cost of Sales

So Rolling 8 months cost of sales for 3/15/2013 would be 300 + 200 + 100 + 5 periods from the previous year.

I've tried using addmonths with a minus condition and can see how that works in a text box (= AddMonths(Now(), -6))

The problem I have is that if I use MyDate then only the one row is selected.

In my programming world I'd need two loops to solve this

Outer one with unique MyDate Values #A

Inner one to loop through all the records testing the myDate value against #A and #A - 8 periods to accumulate to a new field.

Could be I'm just thinking about this wrong, you assistance greatly appreciated.

Thanks,

Jim

[Gysbert_Wassenaar · Accepted solution]

See this discussion for how to deal with this kind of problem.

[#3]

Thanks and sorry for the delayed acceptance of your Answer.  The proposed answer was a little complicated initially, but after debugging with small steps I 'mastered' it.