New Sample: Report_SortAsPerGrpCalc

Report_SortAsPerGrpCalc

by A.D. Tejpal

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.

Version: Access 2000 file format.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=525.

.