Count Clients in Heading

[Vayse]

Hi
I need to do some counting on a report. The report is based on a query, with
one side as the Client table, linked to an Audit table. Each Client can
have a director. So my grouping is like this
Client_DirectorID - GroupHeader1
ClientID - GroupHeader2
AuditID - Detail

In the Director heading, I'd like to count the number of Audits, and the
number of Clients. Count(AuditID) works fine for the Audits. However,
Count(ClientID) will return the same value as Count(AuditID).
I can get the count of the client ID by including a running sum in the
ClientID heading, and putting that value in the Director footer.
How do I go about getting the count of the ClientID in the Director heading?
Thanks
Vayse

 

[Marshall Barton]

I need to do some counting on a report. The report is based on a query, with
one side as the Client table, linked to an Audit table. Each Client can
have a director. So my grouping is like this
Client_DirectorID - GroupHeader1
ClientID - GroupHeader2
AuditID - Detail

In the Director heading, I'd like to count the number of Audits, and the
number of Clients. Count(AuditID) works fine for the Audits. However,
Count(ClientID) will return the same value as Count(AuditID).
I can get the count of the client ID by including a running sum in the
ClientID heading, and putting that value in the Director footer.
How do I go about getting the count of the ClientID in the Director heading?

This is a tricky situation. As you've seen, it is difficult
to use a value before it has been calculated. The trick is
to force the report to be processed twice, once to calculate
and save the value and a second time to retrieve the saved
value and diaplay it.

The first step is to add a text box that refers to the Pages
property (e.g. =Page & " of " & Pages). This is the common
expression that forces Access to run through the report once
to figure out how many pages there will be and again to fill
in the value in the text box.

You can now add code to the group footer's Format event to
save the value and add code to the group header Format event
to retrieve the value. The general idea is:

Private colVals As New Collection

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
If Me.Pages = 0 Then
colVals.Add Me.txtRunCnt.Value, CStr(Me.DirectorID)
End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
If Me.Pages > 0 Then
Me.txtTotal = colVals(CStr(Me.DirectorID))
End If
End Sub

txtRunCnt is the running sum text box and txtTotal is the
group header text box.

 

[Vayse]

That should do it. Thanks.

This is a tricky situation. As you've seen, it is difficult
to use a value before it has been calculated. The trick is
to force the report to be processed twice, once to calculate
and save the value and a second time to retrieve the saved
value and diaplay it.

The first step is to add a text box that refers to the Pages
property (e.g. =Page & " of " & Pages). This is the common
expression that forces Access to run through the report once
to figure out how many pages there will be and again to fill
in the value in the text box.

You can now add code to the group footer's Format event to
save the value and add code to the group header Format event
to retrieve the value. The general idea is:

Private colVals As New Collection

Private Sub GroupFooter1_Format(Cancel As Integer,
FormatCount As Integer)
If Me.Pages = 0 Then
colVals.Add Me.txtRunCnt.Value, CStr(Me.DirectorID)
End If
End Sub

Private Sub GroupHeader0_Format(Cancel As Integer,
FormatCount As Integer)
If Me.Pages > 0 Then
Me.txtTotal = colVals(CStr(Me.DirectorID))
End If
End Sub

txtRunCnt is the running sum text box and txtTotal is the
group header text box.