Developer Forum

I have been inputting employee timesheet data into a table for the last 3
months.
I have been able to write a query to give the total hours worked in this
time, but I am struggling to develop a query to give me the average hours
worked for each employee over the last rolling 12 weeks.
Please help but keep it simple - thanks.

If your data is stored like this --
EMP WorkDate WorkHours
1 2/2/2008 4.5
2 2/2/2008 9
1 2/3/2008 7
2 2/3/2008 6.5

Then use this query and name EMP_Week_Hrs --
SELECT EMP Format([WorkDate], "yyyyww") AS Worked, Sum([WorkHours]) AS Hours
FROM YourTable
WHERE WorkDate Between Date() And DateAdd("ww", -12, Date())
GROUP BY EMP, Format([WorkDate], "yyyyww");

SELECT EMP, [Hours]/12 AS Avg_per_week
FROM EMP_Week_Hrs;

--
KARL DEWEY
Build a little - Test a little