Sorting within grouping

[ironwood9 via AccessMonster.com]

I have a report showing sales data - it is grouped by Region, then Area -
within each area grouping it shows individual sales in detail.

I want the report to show in descending order the highest area in terms of
sales - but when I group in VIEW / SORTING & GROUPING, access asks for
ascending / descending.

I need to go from this:

West Region
Anaheim
East Anaheim .............5
La Palma......................7
Bolsa Chica
Paradise......................3
Almost Paradise .......2
Cucamonga
La Palma ....................97

to something like this - Cucamonga area is shown first, because it's sales
are highest

West Region
Cucamonga
La Palma ....................97
Anaheim
East Anaheim .............5
La Palma......................7
Bolsa Chica
Paradise......................3
Almost Paradise .......2

 

[Duane Hookom]

Create a totals query that groups by Region and then by Area and sums sales.
Add this query to your report's record source so you can sort by SumOfSales
for the area.

 

[Marshall Barton]

I have a report showing sales data - it is grouped by Region, then Area -
within each area grouping it shows individual sales in detail.

I want the report to show in descending order the highest area in terms of
sales - but when I group in VIEW / SORTING & GROUPING, access asks for
ascending / descending.

I need to go from this:

West Region
Anaheim
East Anaheim .............5
La Palma......................7
Bolsa Chica
Paradise......................3
Almost Paradise .......2
Cucamonga
La Palma ....................97

to something like this - Cucamonga area is shown first, because it's sales
are highest

West Region
Cucamonga
La Palma ....................97
Anaheim
East Anaheim .............5
La Palma......................7
Bolsa Chica
Paradise......................3
Almost Paradise .......2

There is no way to tell a report to sort on a value the
report itself is calculating. You will have to calculate
the sales for the area in the report's record source query.

SELECT T.region,
T.area,
T.sales,
(SELECT Sum(X.sales) FROM table As X
WHERE T.region = X.region
AND T.area = X.area
) As AreaSales
FROM table As T

Then you can group the report:
region Ascending Group Header
AreaSales Descending
area Ascending Grou Header

 

[ironwood9 via AccessMonster.com]

Marshall and Duane,

Thanks so much !

Steve

I have a report showing sales data - it is grouped by Region, then Area -
within each area grouping it shows individual sales in detail.

[quoted text clipped - 27 lines]

Paradise......................3
Almost Paradise .......2

There is no way to tell a report to sort on a value the
report itself is calculating. You will have to calculate
the sales for the area in the report's record source query.

SELECT T.region,
T.area,
T.sales,
(SELECT Sum(X.sales) FROM table As X
WHERE T.region = X.region
AND T.area = X.area
) As AreaSales
FROM table As T

Then you can group the report:
region Ascending Group Header
AreaSales Descending
area Ascending Grou Header