Need help Calculating Rolling Averages

[efinnen]

Here's the situation, and I'll just state ahead of time any help or guidance is greatly appreciated.

I have a query that pulls out a date and a number (FLOW for whats its worth), and I need to calculate a rolling 3 month (and 12 month, but once if figure out how to do thethree tehthe 12 should be easy) and I don't have a clue in regards to how to get it done.

for example
Date FLOW 3 MOnth

Jan 99 100
Feb 99 150
Mar 99 300 Should be average of Jan/Feb/March
Apr 99 450 Should be average of Feb/March/April
May 99 700 Should be average of March/April/May

yada yad ayda

I have done something similar with 2 linked queries for one specific months, and numerous facilities (whereas above its one facility over a span of months). I'm having a brainfart on how to step through this process.

Let me reiterate any help (or guidance in the right direction) would be greatly appreciated!!!!

Thanks in advance
Eric

 

[MichaelRed]

There are several approaches, and some may be better suited to your situation.

One way would be to do a module/procedure. Do a recordset which include the necessary fields. Step through the recordset. For the first two, just ignore the calc, but accum Flow(0) and Flow(1). On the thrid, add Flow(2), calc the avg and place it back into the recordset. On the next, Replace Flow(0) with the vurrent Flow, redo the avg ...

Repeat till DONE.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over