Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sort by calculated field

Status
Not open for further replies.
icsupt

icsupt

MIS
Jun 14, 2004
150
0
0
US
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]).



 
Sort by date Sort by votes
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]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

caykamanj1966
  • Locked
  • Question
Search by Date and Some Text
Replies
9
Views
91
dhookom
dhookom
monkeysee
  • Locked
  • Question
Group a calculated field by month in Access Report 2
Replies
5
Views
57
monkeysee
monkeysee
lameid
  • Locked
  • Question
#Type! using replace on Long text field
Replies
4
Views
95
lameid
lameid
lameid
  • Locked
  • Question
Get a named Field Value from Report via function call in Report Control source.
Replies
3
Views
41
dhookom
dhookom
Cojoe
  • Locked
  • Question
Display unbound text box with calculated data in report
Replies
1
Views
47
MajP
MajP

Part and Inventory Search

Sponsor

Back
Top