MS Access Query to sum up hours grouped by staff name

[shahram.shirazi]

Hi guys

I am trying to develop a query in access to sum up the 'sum of hours'
by 'staff name', per month.

I would appreciate any help/comments, as i have no knowledge of
databases/or SQL.

An example is as follows.

---------------------------------------------------------------------------------------------------------------
Staff Name SumOfHours Day
Month Year

J Doe 3.5
21 1 2007
J Doe 2.0
22 1 2007
J Doe 4.5
23 1 2007
J Doe 7.5
24 1 2007
J Doe 0.5
25 1 2007


I would like the query to sum up the hours of the 5 rows above grouped
by the 'Staff Name'. However Im not sure how this would be represented
either eg. is it to possible to do the following

-----------------------------------------------------------------------------------------------------------
Staff Name SumOfHours Day
Month Year SumOfHoursWorked

J Doe 3.5
21 1 2007 0
J Doe 2.0
22 1 2007 0
J Doe 4.5
23 1 2007 0
J Doe 7.5
24 1 2007 0
J Doe 0.5
25 1 2007 18 (this no from
adding rows 1-5 SumOfHours from and putting it on the last row for
that Staff members (eg last day - 25).


Again, would really appreciate the help here.

Cheer guys,
Shahram

 

[giorgio rancati]

Hi Shahram,

make a query to sum TotalHours grouped by the 'Staff Name'
----
SELECT
[Staff Name],
MAX(Dateserial(Year,Month,Day)) AS MaxData,
SUM(SumOfHours) AS TotalHours
FROM tblHours
GROUP BY [Staff Name]
----
save it with name qryTotalHours

then make the final query
----
SELECT
tblHours.*,
Iif(IsNull(qryTotalHours.TotalHours),0,qryTotalHours.TotalHours) AS
SumOfHoursWorked
FROM tblHours
LEFT JOIN qryTotalHours
ON tblHours.[Staff Name]=qryTotalHours.[Staff Name] AND
(Dateserial(Year,Month,Day))=qryTotalHours.MaxData
ORDER BY tblHours.[Staff Name],Year,Month,Day