Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting a count in a report (2000(SP3))

    I have a report based on a totals query that has a count of various services used on a month by month basis.
    Eg January: Service A - 22; Service B -11; Service C - 9
    February: Service A - 12; Service B -45; Service C 6

    I want to be able to sort the services in each month from highest count to lowest
    I have a grouping level on Months and a grouping level on Count of Services
    I have set the count of services group to sort descending to no avail.

    It sorts fine in the query. I have tried using the Val function in the query as mentioned in other postings but get error messages (I suspect because I am using the Count function to count the services in the query). I also tried (not very hard) to fiddle with sorting in the group footer's on format event.

    I am assuming the problem is that the numbers in the count are being interpreted as a string, but even so, they are not sorting at all as far as I can see.

    Thanks as always.

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

    Re: Sorting a count in a report (2000(SP3))

    If you have a Totals query with Count as total option for one of the fields, this count is a number, not text.

    As far as I can see, the Sorting and Grouping window should contain two entries:
    - Month, with Sort Order set to Ascending, and Group Header and/or Group Footer set to Yes.
    - Count of Services, with Sort Order set to Descending, and Group Header and Group Footer set to No.

    There should be NO entry for Service itself in the Sorting and Grouping window.

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting a count in a report (2000(SP3))

    It took a little fiddling, but I finally got it to work. Thanks for putting me on the right track. As I suspected, it was a question of asking the query the proper questions and not giving the report conflicting sort requests.

Posting Permissions

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