[b]Data by Week[/b]

[vani65]

Hi,
I have a TimeSheet table with WorkerName, date, Hrs, Job No. as its fields.
For Example, the table has the following data:
WorkerName date Hrs JobNo
A 28/6/04 8.5 1
B 28/6/04 8.5 1
A 29/6/04 8.5 1
C 30/6/04 7 1
A 1/7/04 8.5 2
A 2/7/04 8.5 1
A 3/7/04 8.5 1
I am trying to query weekly total hrs of a worker for a particular job
For eg.
Job No WorkerName Hrs
1 A 42.5
1 A 8.5
1 B 8.5
1 C 7
Could someone please suggest how to get the desired result.
Thanks in advance

 

[PHV]

Something like this ?
SELECT [Job No], WorkerName, Format([date], "ww") As WeekNumber, Sum(Hrs) As TotalHrs
From yourTable
GROUP BY [Job No], WorkerName, Format([date], "ww");

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244