Show total number of a group in another group header

[Guest]

I have a report with three group headers and my detail. Month, Diagnosis,
Patients and detail. I want to show the total number of patients in the
Month Header but I have not been able to. Can someone share the solution??

I have placed controls in various footers and tried referenceing them in the
header but have been unsuccessful. Thank you.

 

[Guest]

I thought there would be some way to just do this in a report, I ended up
building a couple separate queries to total the group, built a report,
inserted it as a sub report and then referenced the sub report control.
Isn't there an easier method??? Thank you

 

[Guest]

Hi Jeff


You can sort you report by month and use something like this is in the month
header

=IIf((RecordsetClone.RecordCount)>0,"patients " &
(RecordsetClone.RecordCount),"No patients")

I have use an "IIF" as you may have no patients in a month. If this is
never the case just remove the IIF section.

 

[Guest]

Wayne, Thank you, that looks slick but I am not sure how to use that. There
is never a case without patients, I am just looking for the total number.
For example in one case, July has 27.

Am I using the RecordsetClone.RecordCount in the control source of a text box?

Thank you

 

[Guest]

Hi Jeff

On a form you could insert a text box and use this as the control source for
a text box in the form’s header.
=(RecordsetClone.RecordCount)


In a report

Create a new calculated field in the query the report will be based on
Call it Month
Set the format to yyyy / mmmm
Note you need to include the year or your report will not know that each
June or July etc is different.

Include this YearMonth field in the report and group/sort by this and add a
header to each group.
Insert 2 text boxes into the month header.
The first header box could have the date (bind it to the date field in the
table) and set the Format to mmmm
In the second box simply use this as the control source
=Count([PatientID])
I have assumed that each patient will only be treated once each month. If
this is not the case then Count something else (treatmentID, etc – or
whatever you use to show the treatments).

Hope this helps

 

[Guest]

Sorry forgot to add

For this bit

Create a new calculated field in the query the report will be based on

For this you will need to bind this new calculated field to the treament
date field in the query like this
[TableName]![DateFieldName]