Sorting within a group

[BLD21]

2007, I have a report which is using a simple select query for the control source. In the report I am using 3 text boxes with expressions in the control source. One is for getting a total count the other is for a total reject count. The last one is used to calcuate the percentage.(reject count/total count).



I am grouping on company name. I want to be able to SORT by the Percentage field within the grouping. Is this possible?

Thanks to all who reply!!

Bruce D.

[kennejd]

I would personally do the calculations in the source query, but you should be able to use the 'expression' option in the group/sort/total area. You will sort on your formula....not on the calculated text boxes in your report....hope that makes sense.

[BLD21]

I agree with using the source query, but I could not get the counting done correctly in the query. I would need a grouping by for each Company, then a record count of all the Codes, and a record count for just the Rejects. I would then have to create an expression for something like TOTAL REJECTS/TOTAL FED for the percentage.


What I am using in the report is a grouping by Company, then I have one text box with =Count([tbl_Rejects]![COMPANY_NAME]) and the other text box with =Count(IIf([SORT_DECISION_CODE]="REJECT",1,Null)). For the percentage I am using the NAME for =[Total Rejects]/[Total Fed]. This works in calcuating the percents but I can't get it right for the sorting from highest to lowest.

Any help greatly appreciated!!

[kennejd]

In the group/sort expression have you tried:
=Count(IIf([SORT_DECISION_CODE]="REJECT",1,Null))/Count([tbl_Rejects]![COMPANY_NAME])

You can also use IIf([SORT_DECISION_CODE]="REJECT",1,Null)) in an aggragate query....just set the group option to count.