Report sorting

[shangman via AccessMonster.com]

I have a report which runs off a query. Within this query i have a pivot
table which computes the average the scores, which are entered into a table
from a form. For example: in the table there are 3 depts, lets say
maintenance, metalset, and office. They are audited each month and given a
score. The query shows the dept and score, the pivot table gives a average of
these scores for each dept. The report shows the dept and avg score. What i
would like to happen is have the avg score sorted from highest to lowest.
When i go into the reports sorting and grouping i do not see the selection
for this avg from the pivot table. The control source (text box) for the avg
shown on the report is "=Avg([score])" . I hope this is enough info to help
solve my problem. If not i can provide more.

 

[Duane Hookom]

You can only sort a report on an expression that involves a single record in
the report's record source. This prohibits the use of values that are
aggregated in a group header or footer.

You can generally create a totals query that calculates the aggregate value
and then join this query into the report's record source query.