rolling average VBA code MS Access
Hi All
Anyone know how to create a VBA function and or SQL statement which will work in Access to find the rolling average as per example below + see excel file for working example in excel. Should work for a unknown amount of records. Not an easy one I think :-). If it can be done without using Record No that might be ok as well.
Would be great if anyone knows.
Thanks
Asta
Fields in Access calculated Field in Query
Hrs, Record No, Rolling Avg,
10, 1, 10.00,
20, 2, 10.00,
30, 3, 10.00,
55, 4, 13.75,
36, 5, 7.20,
26, 6, 4.33,
33, 7, 4.71,
22, 8, 2.75,
33, 9, 3.67,
55, 10, 5.50,
44, 11, 4.00,
99, 12, 8.25,
66, 13, 5.08,
And so on
Software/Hardware used:
Software/Hardware used:
ASKED:
January 21, 2009 1:24 PM
UPDATED:
January 22, 2009 5:40 PM
Answer Wiki:
How about
SELECT Avg(hrs) AS Expr1
FROM table1
WHERE (((table1.record_no) Between iif((select max(record_no) - rolling_average_increment+1 from table1) < 0, 1,(select max(record_no) - rolling_average_increment + 1 from table1)) And (select max(record_no) from table1)));
This works for the last rolling_average_increment rows.
To see all answers submitted to the Answer Wiki: View Answer History.
Actually, your “rolling average” in the example is simply the Hours in each record divided by the Record No in that record. I am not sure why this is of interest…
Usually, a “rolling average” is taken over an interval (the most recent 5 records, for example), which is what Dwaltr’s code is computing. His/her code will generate the same values as your example only if the “rolling_average_increment” is set to 1.
The following shows the differences resulting from setting the rolling average interval to values between 2 and 6 (and the original Example values, which are effectively an interval of 1). Note the radically different values resulting from small changes in the interval size.
Hours,Record No,Example,2,3,4,5,6
10,1, 10.00 , 10.00 , 10.00 , 10.00 , 10.00 , 10.00
20,2, 10.00 , 15.00 , 15.00 , 15.00 , 15.00 , 15.00
30,3, 10.00 , 25.00 , 35.00 , 35.00 , 35.00 , 35.00
55,4, 13.75 , 42.50 , 40.33 , 36.75 , 36.75 , 36.75
36,5, 7.20 , 45.50 , 39.00 , 37.50 , 34.40 , 34.40
26,6, 4.33 , 31.00 , 31.67 , 29.25 , 30.00 , 34.17
33,7, 4.71 , 29.50 , 27.00 , 28.50 , 33.80 , 35.50
22,8, 2.75 , 27.50 , 29.33 , 35.75 , 37.40 , 47.67
33,9, 3.67 , 27.50 , 36.67 , 38.50 , 50.60 , 53.17
55,10, 5.50 , 44.00 , 44.00 , 57.75 , 59.40 , 49.50
44,11, 4.00 , 49.50 , 66.00 , 66.00 , 52.80 , 44.00
99,12, 8.25 , 71.50 , 69.67 , 52.25 , 41.80 , 34.83
66,13, 5.08 , 82.50 , 55.00 , 41.25 , 33.00 , 27.50
What are you trying to accomplish with this?