Sort on Summary Field 2

[dmuroff]

Hi,

I have a report in Access 2007 which I'd like to sort ascending by field generated within the report for average sales.

The report is grouped by Region and then by State. The sort is going A to Z for Region and then by State like this:

Midwest
Illinois 74
Indiana 42
Iowa 12
Kansas 79
Michigan 20
Minnesota 1
Midwest - Average 60

Southern
Delaware 92
Florida 71
Kentucky 89
Louisiana 12
Southern - Average 70

I'd like it to sort by Region average and then by State average like this:

Southern
Delaware 92
Kentucky 89
Florida 71
Louisiana 12
Southern - Average 70
Midwest
Kansas 79
Illinois 74
Indiana 42
Michigan 20
Iowa 12
Minnesota 1
Midwest - Average 60

Is this possible? Any help would be greatly appreaciated.

 

[Remou]

The only way to do this is with a query. If you have problems building such a query, please post details of the tables you are using.

http://lessthandot.com

 

[dhookom]

To expand on Remou's answer, you would need to create a query that groups by region and averages Sales. You can then add this totals query to your report's record source query and join the region fields. This will create the AverageOfSales to sort on.


Duane
Hook'D on Access
MS Access MVP

 

[dmuroff]

Thank you Remou and dhookom!

I created two separate queries with average sales by region and state then added them to the record source of the query.

In the sorting/grouping list I created a sort by region right above the group by region and a sort by state right above the group by state.

Thanks again and have a Happy and Healthy New Year!