How to sort by SUM result (1 Viewer)

[ariansman]

I have made a report such as the following



Studentname yearlycost

John 21000

Steve 27000

Jack 22000

Amanda 20000



The yearlycost is the sum of monthly_cost of each student and is created by the SUM function of the report. How can I sort the report based on the yearlycost? Since yearlycost was not in the report in the first place and was later created by the SUM function of the report, I don’t see an option to sort the report based on the yearlycost.

Thank you

 

[arnelgp]

create a Total query from your table/query and use the New Query
for your report. you can Add Sorting on the Report desgin.

 

[Gasman]

Get the sum in the query and use that in the report sort.

 

[Gasman]

Crossposted https://www.accessforums.net/showthread.php?t=84167

 

[theDBguy]

Reports have their own sorting section. You can actually sort the report any way you like, even by using an expression like Sum.

 

[ariansman]

Reports have their own sorting section. You can actually sort the report any way you like, even by using an expression like Sum.

Sum does not show up in the droplist for sorting. Do I have to get the sum in the query and then sort it in the report?

 

[theDBguy]

Sum does not show up in the droplist for sorting. Do I have to get the sum in the query and then sort it in the report?

No. Query sorts are ignored in reports. You should be able to select Expression from the dropdown and then use Sum() to sort.

 

[ariansman]

No. Query sorts are ignored in reports. You should be able to select Expression from the dropdown and then use Sum() to sort.

I opened the query in Datasheet view. In the Records group, I clicked Totals. In the Total row, I clicked the cell in the field that I want to sum, and then selected Sum from the list. Is this how we should get the Sum expression in the query? But I still do not see the sum in the report design view to sort it.

 

[Pat Hartman]

No. The totals do not persist.

You need to create an actual totals query that sums the data. Then you can sort by the sum column in the report design. You don't put the order by clause into the query. It has to go into the table design properties.

 

[theDBguy]

I opened the query in Datasheet view. In the Records group, I clicked Totals. In the Total row, I clicked the cell in the field that I want to sum, and then selected Sum from the list. Is this how we should get the Sum expression in the query? But I still do not see the sum in the report design view to sort it.

Hi. Sorry for the delay. I wasn't talking about your query. I am referring to your Report. It has a separate area for sorting.