sorting the entire report based on summarized result

[vasu1211]

Ocity ostate transamt rated amt savings
a b 56 43 13
a b 76 30 46
TOTAL 59

c d 40 20 20
c d 20 35 -15
Total 5

My report has hundreds of groupings like above based on Ocity and Ostate.Each group is summarized as shown above based on total savings. Now i want to sort the entire report such that the group with the highest savings comes first in the report. i.e in the descending order of total savings. Can somebody help me out please.

I tried a few things in sorting and grouping box like expressions. it is not working.

 

[Remou]

I think the best bet would be to base your report on a query that includes total savings.

 

[dhookom]

To expand a little on Remou's solution, you must create a totals query that Groups By Ocity and OState and calculates your total savings. Then add this query to your report's record source and join the OCity and OState fields. Add the calculated total to the grid so that it becomes a field in your report where you can use it for sorting.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[vasu1211]

but when i use the query method, i cannot show the detailed records in the report. Right? for example the report would not have fields like transport amt and rerated amount.

 

[Remou]

The query would look something like:

[tt]SELECT Ocity, ostate, transamt, [rated amt], savings, T.STot
FROM tblTable
INNER JOIN (Select Ocity, ostate, Sum(Savings) As STot
From tblTable
Group By Ocity, ostate) T
ON (tblTable.Ocity = T.Ocity)
And (tblTable.ostate = T.ostate)[/tt]