Sort a grouped report on a field other than the group field 1

[cfvcf]

I have a report that is grouped on employee id but I need to sort the group ids in an order that shows the employees with a higher total of accounts in the grouping first. The total is derived by totaling the counts of 3 other fields on the detail line.

I thought that maybe I could code the following in the sorting and grouping box in one continuous string, but it didn't make any difference:
=[qrySelect_Good_Referrals_by_NBK.CountOfReferral_Quality]+[qrySelect_Rejected_Incomplete_by_NBK.CountOfReferral_Quality]+[qrySelect_Rejected_Incomplete_by_NBK.CountOfReferral_Quality]

The employee does show in the sorting and grouping box as sorting ascending, but I do want it to be the grouping.

Any ideas? Thanks!

 

[dhookom]

You can't sort/group by a field or expression that can't be derived from a single record in your report's record source. If you need to sort by an aggregate, you can create a query similar to your report's record source that calculates the aggregate values. Then add the totals query to your report's record source query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cfvcf]

Thanks!