New user question - timesheet query

New user question - timesheet query

Post by bWlrZX » Fri, 05 Sep 2008 23:24:33

I have been inputting employee timesheet data into a table for the last 3
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.

New user question - timesheet query

Post by S0FSTCBERV » Sat, 06 Sep 2008 00:22:01

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;

Build a little - Test a little