Counting Records in Groups

[Ham]

I have a report with three levels of groupings - Division/Department/Employee. The lowest level on the report is a single line which is actually a group footer for Employee - the line being a sum of many individual Employee records. I can get a count of Employees in each Department by using the Running Sum over Group property. And I can get the total number of employees in all Divisions by using the Running Sum over All property. However, I can't find any way to get the total number of employees in a Division. The Running Sum over Group counter resets with each Department. If anyone has any ideas/suggestions, I'd love to hear them. Thanks. -- Ham<br>

 

[JimHorton]

Ham,<br>
If you put a hidden textbox in the footer of Department, and set its value = the field that is the running sum over group of the department, and then set that textbox's Running sum property to Over Group, this will do it.<br>
--Jim

 

[Ham]

Jim, Thanks for the reply. I've done that. That gives me the count for each department, but not for the division. The division count winds being the ame as count of the last department in that division. Thanks -- Ham

 

[DougP]

Well I have a suggestion which requires code.<br>
In a Public module<br>
Create a function that is accessed over and over again.<br>
Call it from somewhere in your report like so<br>
= CountEm(False)<br>
But call it once in the On_Open Event to reset it. by passing a TRUE to it.<br>
Then the counter is stored in a Static variable.<br>
-------------------------------<br>
Public Static Function CountEm(ResetMe)<br>
' This is reset somewhere so the Count1 variable won't keep couting forever.<br>
Dim Count1 As Single<br>
If ResetMe = True Then<br>
Count1 = 0<br>
ResetMe = False<br>
Else<br>
Count1 = Count1 + 1<br>
CountEm = Count1<br>
End If<br>
End Function<br>
------------------------<br>
OK, This will remember the value but calling it in the correct place will be up to you to figure out.

 

[Ham]

Doug - thanks for the tip. I think that will work - I can get a counter going for each logical level. I'll let you know how it turns out. Thanks again. -- Ham

 

[Ham]

Doug - I'm having trouoble implementing this. Here is an exact - Cut & Paste copy of the code I used(it should look familiar to you):<br>
Public Static Function CountEm(ResetMe)<br>
Dim Count1 As Single<br>
If ResetMe = True Then<br>
Count1 = 0<br>
ResetMe = False<br>
Else<br>
Count1 = Count1 + 1<br>
CountEm = Count1<br>
End If<br>
End Function<br>
<br>
I put =CountEm(False) in the control source of a text box in the lowest logical level. However, I immediately get the message: 'There was an error compiling this function. The Visual Basic module contains a syntax error. Check the code, and then recompile it.' I stared at it looking for an obvious error but didn't see any. I cut & pasted the code to a regular VB5 module (below), and it worked fine. What else could be wrong? Thanks -- Ham<br>
<br>
Private Sub Form_Load()<br>
Dim i As Integer<br>
Dim x As Single<br>
Form1.Show<br>
For i = 1 To 10<br>
x = CountEm(False)<br>
Text1.Text = Format(x, &quot;##&quot<br>
MsgBox &quot;Pause&quot;<br>
Next i<br>
x = CountEm(True)<br>
Text1.Text = Format(x, &quot;#0&quot<br>
End Sub<br>
<br>