rolling average VBA code MS Access

35 pts.
Tags:
Microsoft Access
VBA
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    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?
    3,830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following