Results 1 to 9 of 9
  1. #1
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63

    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 offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,791
    is AllAmount in the query?
    if so, then you can sort.

  3. #3
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63
    '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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    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
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  8. #8
    JonathanT is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2011
    Posts
    63
    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,553
    Glad I could help.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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 - Senior Forums