Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sort by Summary Field in Report (2000)

    I am not sure how to do this but maybe there is a way. I have a report that is grouped by client number. There is a gain/loss field that I sum on for each client. Is there a way to sort so that the largest sums of this field are first. I want to sort from largest to smallest sum. This sum is in the Client number footer. Doesn't sound like I can do this in a report.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Sort by Summary Field in Report (2000)

    I think you need to create a new grouping query that does the summing for you, that would just have a client number and the sum of weight loss/gain when grouped by client number.

    Join this query to the query behind your report, and the total weight/gain loss is now available as a field for the report, and can be used for sorting in the report.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sort by Summary Field in Report (2000)

    You can't do this directly, you'll have to create some queries for this.

    Create a query based on the table or report that is the record source of the report. Add only the client number and the gain/loss field. Select View | Totals and change the Total option for the gain/loss field to Sum (leave it as Group By for the client number.) Save this query.

    Create a new query based on the table or report that is the record source of the report and on the query you just saved. Join them on client number. Add all fields you need or * from the first table/query, and the SumOf... field from the query. Save this query too.

    Open the report in design view and set the Record Source property to the name of the query you just saved (the second one.) Activate the Sorting and Grouping window, change the client number field to the SumOf... field, and set sort Order to Descending.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •