Calculate total hours (for all employees) on each department?

[KevinSlater]

Hello, i have a table with the fields: "employee_number", "shiftdate" "department" "basic_hours", and "over_time"

And a query named "employee_hrs" looks at the fields in this table, i would like a new field in the query to be able to calculate the total hours for all employees together but for each department.

eg the total of: basic hours + overtime for department A)
the total of: basic hours + overtime for department B)
and so on for each department

would only like to show one instance of a department (distinct) with the total hours done by all employees for that department?, if any one could please help me out that would be great!?

 

[Jon K]

Select [Department], Sum([Basic_Hours]+Nz([Over_Time])) as DeptTotal
From [TableName]
Group By [Department]


The Nz() is needed if the Over_Time field may contain null values.
.

 

[Murli]

You can use the Group By function in your query on the field department and build an expression basic hours + overtime.

A sample database is attached for your reference.

Murli

 

[KevinSlater]

Thanks Jon and thanks for providing that sample Murli thats a great help.
The group by function was the main thing i missed out, & good tip about using the Nz function as some fields are Null.