Sorting by a calculated control in a group footer

[Dawn Shaferly]

I have a report grouped by an Op number (text). In that
field's group footer, I have a textbox summing the total
hours by Op. I would like the report to be ordered by the
highest hours (desc), but not lose the grouping by the
operation. Example:

Op 20 3 hours
Op 20 2.4 hours
sum([hours]) 5.4 hrs.
Op 30 11 hours
Op 30 4 hours
sum([hours]) 15 hrs.

I would like Op 30 and it's detail lines and the subtotals
to appear first in the list because it has the highest
hours. When I change the order in the Grouping and
Sorting dialog box, it groups by hours and subtotals them
(only adds together all of the 11 hrs, etc...)

 

[Marshall Barton]

I have a report grouped by an Op number (text). In that
field's group footer, I have a textbox summing the total
hours by Op. I would like the report to be ordered by the
highest hours (desc), but not lose the grouping by the
operation. Example:

Op 20 3 hours
Op 20 2.4 hours
sum([hours]) 5.4 hrs.
Op 30 11 hours
Op 30 4 hours
sum([hours]) 15 hrs.

I would like Op 30 and it's detail lines and the subtotals
to appear first in the list because it has the highest
hours.

To sort or group on a calculated value, the calculation must
be done in the report's record source query.

First create a query (naame it ReportData) to apply your
Where clause:

SELECT *
FROM thetable
WHERE whatever

Now, create a totals query (name it HourTotals) that
calculates the total hours for each Op:

SELECT Op, Sum(Hours) As OpHours
FROM ReportData
GROUP BY Op

Then the report's record source can be a third query that
Joins each total to the original data:

SELECT ReportData.*, HourTotals.OpHours
FROM ReportData INNER JOIN HourTotals
ON ReportData.Op = HourTotals.Op

With all that in place, the report can sort by the OpHours
field and group by the Op field.