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 "mikey" wrote: > 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.
