Results 1 to 9 of 9
  1. #1
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118

    Report Sorting on a calculated field

    I have a report that has a RatePlan grouping. The grouping is sorted A-Z. The grouping includes a text box that sums the Amount field called "AllAmount". The report only sorts by the RatePlan grouping. I want to sort the report by the AllAmount text box. Screenshot attached. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    is AllAmount in the query?
    if so, then you can sort.

  3. #3
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    'AllAmount' is not in the query. It a calculated field in the RatePlan grouping. 'Amount' is in the query but that only sorts within a group if I had any fields in the Detail section which I don't. I want to sort the RatePlan grouping by the 'AllAmount' text box and not the RatePlan names

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You will get more responses if you post images of the report design, or even better, a zipped copy of the db. A Word doc with pictures is not of much to go on.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    I removed the proprietary info and uploaded the database
    Attached Files Attached Files

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Took a quick look but I have to go out soon. AFAIK, you cannot sort on this calculated field when it's not part of the report recordsource.

    Your Totals query has no field using an aggregate function (sum, count, etc) so it's not much good for this. Make copies of the report and query. In the original query remove the id field (else it will create too many groups) and change group by for Amount to sum. Sort by SumOfAmount ascending and run it. If it looks good, the existing report might give you what you want. However, if the query calculated field becomes "SumOfAmount" you will have to change "Amount" to that in every control that refers to Amount. As such the query will still have 2 groups, which is probably going to be an issue. Will take a look later to see if anyone finishes this for you. Gotta run.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Clarification required: if you want to sort ascending or descending on the AllAmount sums only (e.g. $16k, $13k, $6.3k) ignoring the RatePlan you can do that. You cannot have this sort AND Group on Rate Plan. See if the attached is what you're after.

    SORTING2.zip
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    JonathanT is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    118
    Micron, Changing the Group to Sum the amount in my query worked perfectly. I actually created another query rather than use the original one. As you suggested, using the original query would have required a large number of changes in other reports and forms. Thank you

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad I could help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How to sum a calculated field on a report?
    By Athar Khan in forum Reports
    Replies: 1
    Last Post: 03-12-2017, 10:25 AM
  2. Sum of calculated field in report
    By whisp in forum Reports
    Replies: 5
    Last Post: 02-12-2015, 02:56 AM
  3. Sorting a Report field based on its value
    By mindbender in forum Reports
    Replies: 5
    Last Post: 09-30-2014, 01:40 PM
  4. Sorting a Report by a Calculated field
    By mulefeathers in forum Reports
    Replies: 1
    Last Post: 05-22-2010, 08:21 PM
  5. sorting a field in report
    By Philangr8 in forum Reports
    Replies: 3
    Last Post: 08-26-2009, 05:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums