[AccessD] Sort By Sum on Footer

Mark,

    On a report having group header & footer for names, you wish to sort this group as per descending order of sum displayed in its footer. Two alternative solutions are available for getting the desired results.

    First solution:
    (1) In the Sorting & Grouping dialog box create a group with following entry in the first column (titled Field/Expression):
        =Format(Nz(DSum("Score","T_Data","PName = '" & [PName] & "'"),0),"0000") & [PName]
    (2) In the second column (titled Sort Order), select Descending.
    (3) Treat this group header & footer just as if it were for PName.

    Note: 
        (a) You could substitute DSum() function by a user defined one, if so desired.
        (b) It is presumed that the sum would not exceed 4 digits. Otherwise, format string used above, can be expanded suitably.
        (c) Field names have been changed to PName & Score respectively so as to avoid any potential conflict with reserved words. T_Data is the assumed name of source table.

    Second solution:
    (1) Add a calculated field named TotScorePName in the source query, as per sample SQL given below.
    (2) In the report, create a group as per TotScorePName (Descending) - with group header & footer.
    (3) Treat this group header & Footer just as if it were for PName.

    Note:
        (a)  You could substitute DSum() function by a user defined one, if so desired. However, use of a subquery is not recommended as such queries are found to cause problems when used as record source for reports involving group levels.
        (b) Other points are similar to those mentioned against first solution. 

Best wishes,
A.D.Tejpal
---------------

Sample query - (As record source for the report)
=====================================
SELECT T_Data.*, Format(Nz(DSum("Score","T_Data","PName = '" & [PName] & "'"),0),"0000") & [PName] AS TotScoreName 
FROM T_Data;
=====================================

  ----- Original Message ----- 
  From: Mark A Matte 
  To: accessd at databaseadvisors.com 
  Sent: Friday, August 11, 2006 20:12
  Subject: Re: [AccessD] Sort By Sum on Footer


  Sorry...Here's an example:
  NAME   COUNT
  Bob        23
  Bob        14
  Mary       10
  Mary       40

  Each name has a footer that has Sum(count)...What is the easiest way to sort on this sum in the footer...so Mary's group would be listed first if sorting descending?

  Thanks,

  Mark


  >From: "Susan Harkins" <ssharkins at setel.com>
  >Reply-To: Access Developers discussion and problem 
  >solving<accessd at databaseadvisors.com>
  >To: "'Access Developers discussion and problem 
  >solving'"<accessd at databaseadvisors.com>
  >Subject: Re: [AccessD] Sort By Sum on Footer
  >Date: Thu, 10 Aug 2006 19:03:09 -0400
  >
  >So, you're sorting pages?
  >
  >Susan H.
  >
  >Other than creating a query that gets the sum and join it to the query I'm using as a recordsource...Is there a way to sort by the sum on a footer in a report...sorting/grouping doesn't seem to like the idea...any ideas?