Summing Grouped Totals

[Drew]

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) and COUNT(NumEmpDept), but that returns a
#Error. How can this be done?

Thanks,
Drew

 

[Duane Hookom]

Make sure the name of the text box is not also the name of a field in the
report's record source.

 

[Drew]

Well, the text box was named Text21, and there are no fields named Text21.
I tried renaming it to something else, but that didn't work either.

Thanks for you response, but I'm still looking for a solution

Thanks,
Drew

 

[Duane Hookom]

Is your text box in the Page Footer or Report Footer section?

 

[Drew]

It is in the Page Footer section of the report. I am doing the grouping in
the query so I didn't have a group header and footer to put it in.

Thanks,
Drew

 

[Drew]

Not sure if this makes a big difference, but this app is an ADP not a MDB...

thanks,
drew

 

[Duane Hookom]

Take the control out of the Page footer and put it in the Report Footer.

 

[Drew]

PERFECT! Thanks for your help!

Drew

Take the control out of the Page footer and put it in the Report Footer.

--
Duane Hookom
MS Access MVP


E.EmpActive