How do I count records in a group and sort on that number?

[Guest]

I have a report that gets its data from a query... the report is grouped and
the number of detail records that follow each group is different... I want to
count the number of detail records in each group and then sort the groups
according to the number of detail records in that group in descending
order... Any help with this would be greatly appreciated...???

 

[Guest]

For report groups, you can include functions like AVG, SUM, COUNT, etc. in
the Group Footer. You would then use the Count function on the primary key of
the records being displayed in the group.

When you create the group, have it sort on this Count field and set it in
Descending order.

-Shane

 

[Guest]

Shane... I appreciate your comment and I think this is what I want to do...
But I am new to the Access game and I was wondering if you could give me an
example that I could follow that shows me how to setup the count function in
my report...???

 

[Guest]

If you create the report using a wizard, you'll be given the option when you
create a group to add in these functions for the groups.

If you have a group footer already, create a Text Box in this footer. For
the Control Source, put "=Count(<record name>)", where <record name> is the
name or id of the record and of course do not include the quotes. Now, for
each group, you will get a count of those records in that group. However, the
problem is that Sorting and Grouping only works on values from the query you
pulled and this count value is not part of the query. So I'm at a loss on
helping you with that.

The only other method is to inlude VB code that manually counts the records
that, for example, are widgets, then counts the ones that are gidgets, and so
on. Then you would have to store this number in some stable to be pulled by
the query. Then you would be able to Sort based on this number. Hope this
helps.

-Shane