Sort by calculated field

[icsupt]

I have a report that I have a field
[Total/Project Balance].

I also have lines for CustName that each have a Total/Project Balance. There may be many lines for each CustName, each with their corresponding Total/Project Balance. I want to total up all of the [Total/Project Balance] for the CustName and then group it together and then have an ascending sort by Total/Project Balance. In other words, for CustName "ABC Company" when I sum all of the Total/Project Balance, it would come to $20,000. For CustName "XYZ Company" when I add up all of the Total/Project Balance, it would come up to $30,000. So I would want ABC Company and all of its lines to come first and then XYZ Company to come under that.

Can you tell me how I would accomplish this?

Thanks in advance.

But the [Total/Project Balance] field is a calculated field, in other words, in the Custname header, I have =Sum([Total/Project Balance]).

 

[dhookom]

You can create a totals query similar to your report's record source but Group by CustName (or preferably the primary key field) and sums the [Total/ProjectBalance] field. Add this query to your report's record source and join the Customer fields.

This allows you to add the SumOfTotalProjectBalance to the report's record source so that you can sort on it.

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]

 

[jmhicsupt]

I am revisiting this as I would like to sort by calculated field. I am stumped!

My field names are:
[categorytobesorted]
[Mgrscore]
[valuetobemultiplied]

I want to first sort on the [categorytobesorted] and then sort on the [mgrscore]*[valuetobemultiplied].

I tried in query, but it still does not work.

I know the answer is above, but I'm not understanding how to do it.

Thanks for any help I can get.

 

[dhookom]

Sorting in reports must be done in the Sorting and Grouping dialog. It looks like you don't need to sort on a summary value. Try sort by:
Field/Expression
[CategoryToBeSorted]
=[mgrscore]*[valuetobemultiplied]

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]

 

[jmhicsupt]

Excuse me if I'm asking dumb question.

I went to View Sorting/Grouping
Under Field/Expression, I typed in
[Categorytobesorted] Descending
=[mgrscore]*[valuetobemultiplied] Descending

Was I supposed to actually type "=[mgrscore]*[valuetobemultiplied] as another category under View/Sorting?

Thanks a million times.

 

[dhookom]

The two expressions I provided should each be on their own line/level in the sorting and grouping dialog.

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]