This sample db
demonstrates two alternative styles for sorting between groups as per total
sum for each group, as follows:
(1) Report R_SortByGrpSumDesc_A
makes use of calculated expression in Sorting and Grouping (S&G) dialog
box. The report is grouped as per publishers and these groups are sorted in
descending order as per total group amount (Stock * UnitCost) for each
publisher. The expression used directly in S&G dialog box is:
=CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher
= '" & [Publisher] & "'"),0))
(2) Report R_SortByGrpSumDesc_B
makes use of calculated field named GrpAmount in the source query.
This field name is used directly in S&G dialog box. The expression in
the source query (design grid) is:
GrpPrice:
CLng(Nz(DSum("Stock * UnitPrice","T_Books","Publisher
= '" & [Publisher] & "'"),0))
Note:
(a) For (2)
above, use of subquery (instead of DSum()) for arriving at the total
value won't suit, attracting an error message while trying to run the
report. This is because subqueries are not amenable to direct use as
source fields for group levels in an access report.
(b) For
consistent sorting results, output of Nz() function, when used in a query
or S&G dialog box, is required to be converted to desired data type.
Imp:
(a) Name of a calculated control can not be
used directly in Sorting and Grouping dialog box, which only accepts
either a field name featuring in the record source or an
expression.
(b) User defined function deriving its value by
referring to the calculated control itself, is also not found effective
in Sorting and Grouping dialog box. This could be attributable to the
fact that values held by various controls are not yet
exposed in report's open event, whereas field settings for sorting
and grouping are required to be enforced at this stage itself.
(c) For a solution independent of source query, an expression
based upon either a built in or a user defined function can be
used in the
Sorting and Grouping dialog box, as demonstrated in report style A in
this sample db.