New user question - timesheet query

M

mikey

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.
 
K

KARL DEWEY

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;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top