Rolling Average previous 12 months then 1 month rolling average

[mabuhay87]

I have a query with EmployeeID, PayDate, HoursPerPeriod.

Example:

EmployeeId PayDate HoursPerPeriod RollingAverage

-----1-----------10/8/2012-------35.28
-----1-----------10/22/2012-----42.96
-----1-----------11/4/2012--------41.92
-----1-----------11/18/2012--------32.66
-----1-----------12/16/2012--------35.65
-----1-----------12/30/2012--------36.0
-----1-----------1/14/2013--------43.5
-----1-----------1/28/2013--------32.66
-----1-----------2/11/2013--------39.84
-----1-----------2/25/2013--------31.03
-----1-----------3/11/2013--------38.08
-----1-----------3/25/2013--------35.12
-----1-----------4/8/2013---------38.84
-----1-----------4/22/2013--------37.79
-----1-----------5/6/2013---------39.44
-----1-----------5/20/2013--------38.04
-----1-----------6/3/2013---------37.48


-----1-----------6/17/2013--------48.56
-----1-----------7/1/2013--------39.44
-----1-----------7/15/2013--------36.71
-----1-----------7/29/2013--------34.62
-----1-----------8/12/2013--------36.19
-----1-----------8/26/2013--------35.12
-----1-----------9/9/2013----------31.68
-----1-----------9/23/2013---------34.42
-----1-----------10/7/2013--------36.93------------38.0
-----1-----------10/21/2013-------38.06------------37.0
-----1-----------11/4/2013--------34.23------------36.0

----so on and so forth with different EmployeeID.

1) How to get the 12 month rolling average from 10/8/2012 to 9/23/2013
and results will be shown on 10/7/2013 under Rolling Average field?

2) How to get the rolling average by paydate after 10/7/2013?

This is the rolling average based on employeeID.

Please help.

Thank you.

[burrina]

Are you wanting a hard coded solution, or a variable? I generally use a calendar to set my dates. Are you ONLY using PayDate as your date criteria? Are you wanting to query by period, define what a period is if so. Please explain a little more.

Between [Forms]![frmYourFormNameHere]![StartDate] And [Forms]![frmYourFormNameHere]![EndDate]

[mabuhay87]

I tried to create a query of all employees' hours per period(every two weeks PayDate). I used PayDate as date criteria. What I am trying to accomplish is to get the calculations of rolling average hours per PayDate from previous 12 months(10/1/2012 to 9/30/2013) starting on 10/1/2013 then calculations of rolling average continue the next PayDate.


Please help and guide me.

Thank you.