Calculated controls

[Guest]

I have a report which summarizes sales by Product Code. That is, each line
in the report shows total sales for a particular product for the select
period. This information is displayed in the ProductCode Header section of
the report while the Details section is not visible. I would like to order
the lines in the report decending from the product for which the total sales
value is greatest, i.e. order the lines in the report based on a calculated
field. Is this possible and if so how?

Cheers

 

[Allen Browne]

Access calculates the totals as it goes, so it cannot back up and rearrange
the way the items are sorted after that.

This means you need to get the totals into the source query so there is a
field to sort on. You could get the total from a subquery or a DSum()
expression. Subqueries often give a 'multi-level group by error' if you
include them under a report, and DSum() is slow.

Another approach might be to use a main report for the totals, with a
subreport to list the details. The issue of this approach is how you filter
the subreport to the same as the main report.

1. Create an unbound form where you enter the limiting dates.
Add 2 unbound text boxes named (say) txtStartDate and txtEndDate.
Set their Format property to General Date so Access knows the data type and
will not accept invalid dates. Save the form.

2. Create a query to use for the report
Depress the Total button in the toolbar.
Access adds a Total row to the design grid.
Set up your fields like this:
Field: ProductID SaleDate Quantity
Total: Group By Where Sum

3. In the Criteria row under the date field, enter:

= [Forms].[Form1].[txtStartDate] And < [Forms].[Form1].[txtEndDate] + 1

4. Declare the parameters:
Choose Parameters on the Query menu.
In the dialog Access opens, enter 2 rows, e.g.:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time

This query gives you the totals, so you can sort your report by
SumOfQuantity descending (using the Sorting And Grouping pane in the
report.)

You can now create a subreport to list further details under each product if
desired. If the subreport's query also reads the dates from the text boxes
on the form, the results will be consistent.