Sort by Calculated Field in Group by Header

[jlg5454]

Is there a way to sort by a Calculated Field in the Group by Header. When I try this, it keeps telling me you can not sort on aggregate fields? What can I do?

 

[dhookom]

You can sort on calculations/expressions derived from a single record in the report's record source query. If the value you want to use is not in the record source query then you must add it to the record source.

If you don't know how to do this, you need to provide some significant information about your record source and what value you want to sort on.

Duane
Hook'D on Access
MS Access MVP

 

[jlg5454]

Hmm..Here is my Report. The record source is a query. There is a Group by CallerID section and a calc total next to it by sum of calls(which is calulated from the query by counting sub category). The detail section goes like this: (Fields from query Record source) - Category, Sub Category and Total Calls. So what I would like to do is sort desc on the Call field(calulated by summing the total calls field in the detail section) in the Caller ID header section. How can you do this? I did some search and some people suggest creating a total query. How do you join the total query to the Report's record source??

Thanks!

 

[dhookom]

I was hoping to see the SQL of your record source query. However, you should save a copy of your record source query as qtotCallsByCallerID. This query should count the Sub Category. Don't save the report with this new query as its record source.

Make this new query into a totals query so the lowest level of detail is on the CallerID field. Open the query designer for the report's record source and add qtotCallsByCallerID and join the CallerID fields as well as any other higher level fields. Drop the Count of Sub Category into the grid so the column is made available in your report's field list. You can then sort by it.

Duane
Hook'D on Access
MS Access MVP