SubGrouped Percent Rate

[awl]

In a report I have 3 Groups of People. In each group are Grouped Responses, which are a possibility of 5 Responses. These Grouped Responses have a Number of Responses. There is a Group People Footer in which I “Subtotal” all the Responses per Group. There is also a Report Footer with a “Grand Total” of Responses which equals 289 per example below. My control source for both “Subtotal” and “Grand Total” in both People Footer and Report Footer is =Sum([CountOfTakeResponse]) I want to display the Percentage Rate within each Group of the Responses. For example Group A: Response 1 has 5 records which is 9% of 55; 10 is 18% of 55; 5 is 9% of 55; 12 is 22% of 55 and 23 is 42% of 55. The control source for the Text Box “Percent Rate (%)” in the detail section along side the Response is: =([CountOfTakeResponse]/Sum([CountOfTakeResponse]))*1 However, the Percent Rate displayed is based off of the Grand Total of 289, not of the Group Subtotal, ie 55. How can I get the Percentages to reflect the Group Subtotal and not the Grand Total?

Group A: Response 1 = 5, Response 2 = 10, Response 3 = 5, Response 4 = 12, Response 5 = 23; Subtotal: 55
Group B: Response 1 = 23, Response 2 = 18, Response 3 = 5, Response 4 = 8, Response 5 = 14; Subtotal: 68
Group C: Response 1 = 21, Response 2 = 10, Response 3 = 8, Response 4 = 112, Response 5 = 15; Subtotal: 166

Thanks for any comments. Curtis

 

[randysmid]

Hi,
This formula belongs in the OnFormat event of the Group Footer for your "Groups":
=([CountOfTakeResponse]/Sum([CountOfTakeResponse]))*1 HTH,
Randy Smith
California Teachers Association

 

[awl]

Somehow it is not working. I opened the properties of the Group Footer, in the On Format event, I entered the formula as suggested: =([CountOfTakeResponse]/Sum([CountOfTakeResponse]))*1 and get an error reading consisting of 2 causes: The function you entered can’t be used in this expression. 1) The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. 2) There may have been an error evaluating the function, event, or macro. Based on this error reading, the report is not generated.

Note: I don’t have a macro to support this function. If a macro is required, a recommendation would be needed as far as the text is concerned. I would prefer a macro, since I am not familiar with VBA.
A further note: In my detail section of the report, I have 4 text boxes, 1) Group Name, 2) Response, 3) CountOfTakeResponse, and 4) Percent Rate which is the formula =([CountOfTakeResponse]/Sum([CountOfTakeResponse]))*1 Thank you for your help.

 

[randysmid]

Hi,
From what you described, it sounds like you don't want any kind of detail section being printed. You can keep the Detail section, but simply set the Visible property to No. (For testing purposes, you can keep the Visible property to Yes).

Your people footer will have the necessary labels, e.g., lblGroup1.caption = "Group ", with a text box next to it containing the GroupName, e.g., "A" as shown in your example.

You wrote:
In my detail section of the report, I have 4 text boxes, 1) Group Name, 2) Response, 3) CountOfTakeResponse, and 4) Percent Rate which is the formula =([CountOfTakeResponse]/Sum([CountOfTakeResponse]))*1

These 4 text boxes belong in the group footer. HTH,
Randy Smith
California Teachers Association

 

[awl]

Thanks for responding, I hope my notes weren’t confusing. I provided the names of the 4 text boxes to give you an idea of what data I needed displayed/printed. In the Group Footer all I need is the Group Total # of Responses, which is no problem. It is in the Detail Section, that the 4th text box, the Percent Rate is where I have the problem; maybe the formula is not correct. With your suggestion, I tried putting all 4 boxes in the Group Footer, however, only 1 of the 5 responses displayed in each of the groups, plus the Percentages were off. I could send a condensed version of the database, if it would help. Thanks.