How do I sort a report by field in Access?

[Guest]

I am creating a crosstab report that includes a primary sort of 4 fields [Job
Type], a secondary sort [Job Description] and a tertiary sort [ Service
Description ] and a final sort [ Amount Billed ]. In the [ Job Description ]
Footer I added a text box with the following Formula "=Sum([Amount Billed])"
Which gives me the subtotal per Job Description . I would like to know if I
can sort the entire report by text box that I created so that I can see my
most expensive jobs first and the least expensive jobs last?

 

[Allen Browne]

You cannot sort the report by a calculated control. Access does not have the
values in all the text boxes until it has finished the report, and by then
it's too late to sort the report by those values.

That means you need to somehow get that calculation into the query that
feeds the report if you wish to sort by it. You might be able to add another
column to your crosstab (call it a RowHeading), and use an expression that
calculates the total (using DSum() or a subquery.)

If that is not practical, you could try creating another query that uses
your crosstab as an input "table", and add the calculated field to this
query. Then use it as the source for the report. Stacking one query on
another like this will probably be the best idea, since DSum() is slow and
if you try to sort a report by a subquery field Access sptis the dummy with
a "multi-level group-by" error.