Report order by quantity?

  • Thread starter Thread starter Mike Green
  • Start date Start date
M

Mike Green

Hi all
I have a report and use a field "=Count(*)" in a group heading to count the
number of records found for that particular group.
Is there a way to now get the report to order by the count field, so that
the group with the highest number of records is first (or last) in the
report?

Thanks in advance

Mike
 
Mike said:
I have a report and use a field "=Count(*)" in a group heading to count the
number of records found for that particular group.
Is there a way to now get the report to order by the count field, so that
the group with the highest number of records is first (or last) in the
report?
Click to expand...


To make a report sort on a group count (or sum), the count
must be calculated in the report's record source query.
There are various ways to do that, but which one is
appropriate in your case depends on the data in your current
record source table/query. One general way is to create a
separate query that only selects the grouping field and the
count. E.g. If your existing record source is a query
named rqry:

query GroupCounts
SELECT grpfield, Count(*) As GrpCnt
FROM rqry
GROUP BY grpfield

Then set the report's record source to another query that
Joins that query to rqry:
SELECT rqry.*, GrpCnt
FROM rqry INNER JOIN GroupCounts
ON rqry.grpfield= GroupCounts.grpfield

Then the report can use Sorting and Grouping to sort first
on the GrpCnt field then on grpfield.
 
Back
Top