Sort by Subreport

[Guest]

Hi,
I want to display the following in a report:

Category 1 6
Indication 1 1
Indication 2 2
Indication 3 3

The number to the right of Category 1 is a sum of all the indications within
the category. I currently have a report that displays Category 1, the
Indications and each Indication's count. I have a subreport that lists the
Category count (6 in this case) when each category is displayed. It currently
sorts alphabetically by category but I want to sort by the Category count
which is a subreport.
Is there any way to do this?
Thanks a ton!
Brett

 

[Allen Browne]

Access cannot sort by something that it has not even calculated until all
the categories are finished. You must get the total (the 6 in your example)
into the RecordSource of the main report if you want to sort on it.

Presumably you have 2 tables: one with the categories, and one with the
indications.
Create a query that uses both tables.
Depress the Total button on the toolbar (upper sigma icon.)
Access adds a Total row to the grid.

Under the fields from the Categories table, accept Group By.
Under the Indication field, chooes Sum.
This query now shows one record for each category, and the sum of
indications.

Once you have that working, switch your report to design view, and change
its RecordSource property to the name of this query. You now have a
SumOfIndications fields that you can sort on (Sorting And Grouping dialog on
the View menu.)