Report calculations

[DoctorJDM]

A report displays hours spent on various activities for staff members, grouped according to their supervisor.

All the calculations have to be done in the report, whose structure is

Supervisor Header
Detail
StaffName Date Activity1Hours Activity2Hours Activity3Hours
David Smith 1/1/08 12 14 0
David Smith 3/1/08 6 0 0
David Smith 7/1/08 2 5 0
-- -- --
StaffName Footer 20 19 0

Alice Jones 3/1/08 7 0 11
Alice Jones 9/1/08 3 0 7
-- -- --
StaffName Footer 10 6 18
-- -- --
Supervisor Footer 30 25 18

I need now to show the number of staff making up the hours totals in the Supervisor Footer, which in the example would be 2,1 and 1. Finding this very tricky.

I tried creating unbound fields in the StaffName footer for each activity with sources, for example,

Unbound field : Activity1HoursTotal
Source : =IIf (Sum(Activity1Hours>=1,1,0)

This works fine but I can't now sum these unbound fields to get a staff count in the Supervisor footer.

Any thoughts?

 

[dhookom]

You can either do this with multiple group by queries that do the work and then are added into the report record source or use a running sum.

Text box in staffname header:
Name : txtActivity1Count
Control Source : =Abs(Sum(Activity1Hours>0))
Running Sum: Over Group
Visible: Not necessarily

Then add a text box in the supervisor footer:
Control Source: =txtActivity1Count

Duane MS Access MVP
Now help me support United Cerebral Palsy