Anyway to sort by subtotal? 2

[PaulSligar]

I've created a report in Access that has detail numbers and subtotals/sums of those detail numbers in the group footer. All looks fine, except... I want to sort (descending) the report based on the subtotals.

For instance, I have table with Name, Month, and OverTimeHours in it. I can show the detail of OverTimeHours by Name and Month, with subtotal of OverTimeHours by Name. I want to display/print this report sorted by the Name with the highest subtotal of OverTimeHours, followed by the next highest, etc.

I'v tried setting the Orderby property in the Report section to '=SUM([OverTimeHours])', but that doesn't seem to have any effect.

Any ideas?

 

[scriverb]

Change your RecordSource property of your Report to a query. In the query create an additional column using the DSum function to select the Overtime detail values for each record and Sum them up. Name the column SumOfOverTime. Now you have a value that can be sorted in the GroupingAndSorting section of the Report by this Value.

You can just leave your report setup the same and by sorting by this calculated value in the query your report will be in the correct order as you requested.

Post back if you need any more assistance with this query.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[dhookom]

A modified version of Bob's answer is to create a totals query that groups by Name & Month and totals overtime. You can then add this query to your report's record source query and join the Name and Month fields. This allows you to drop the SumOfOvertime into the grid and use it in your report. This method is usually more efficient compared with DSum().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.