Summing Grouped Totals

[dr00bie]

I have a report that uses the following query,

SELECT COUNT(P.PosDeptID) AS NumEmpDept, D.DeptName
FROM EmpCore.dbo.tblEmployee E INNER JOIN EmpCore.dbo.tblPosition P ON E.PosID = P.PosID INNER JOIN
EmpCore.dbo.tblDept D ON P.PosDeptID = D.DeptID AND E.EmpActive = 1
GROUP BY P.PosDeptID, D.DeptName

Here is the data from this query,

2 Assistant Director, Administration
17 Buildings and Grounds
7 Community Services
2 Director
9 Fiscal
36 Food Service
41 Habilitative Services
18 Housekeeping
4 Human Resources
5 Information Technology
36 Medical Services
12 MR/MI Unit
4 Program Support Services
5 Purchasing and Store
11 Research and Training
3 Residential Records
1 Risk Management
5 Security
69 Unit I
85 Unit II
81 Unit III
1 Volunteer Services
1 Central Office (Human Rights Advocate)

It lists the department and the count of employees in the department. I need to display a total of employees.

I have tried, =SUM(NumEmpDept), but that returns a #Error. How can this be done?

Thanks,
Drew

 

[dhookom]

This question sounds a bit familiar. Did you try place the text box in the Report Footer and make sure the name of the text box is not also the name of a field?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[dr00bie]

Cross posted this topic here first, and didn't get any replies, so I posted to the newsgroup... Sorry Duane... Today I am going to redo the report and the query and do the grouping in the report instead. Thanks for your reply and sorry about the cross post.

Drew

 

[dhookom]

I don't mind the cross post. I was just hoping that you had gotten a sastisfying answer.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.