Sorting by a calculated control in a group footer

  • Thread starter Thread starter Dawn Shaferly
  • Start date Start date
D

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...)
 
Dawn said:
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.
Click to expand...

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.
 
Back
Top