Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Reports _ Sort On Volumes Per Group?

Posted by: shellp Jan 10 2017, 08:09 PM


I am using Access 2010. I am trying to create a report where I group by City but sort by the volumes per city. So if Toronto had 36, Montreal 48 and Moncton 24, I want to sort descending so the resulting order is Montreal, Toronto and Moncton.

I am grouping on city, creating a field of Count(ID_Number) and creating a sort expression for Count(ID_Number). I even tried grouping in a query and basing the report on that with the query doing the descending sort and it wouldn't work.

I am also miffed why the "with A on top" of the group is present suggesting to sort by city name but that makes no sense. I'm sure this may be easy and I'm missing something due to sleep deprivation. All assistance greatly appreciated.

Posted by: RJD Jan 10 2017, 08:42 PM

Hi: Here's one way to do that ... include the total city counts in the query for each city, combine that with the city name (in case there are ties) and use that as the group in the report. You can show just the city name if you want, instead of the sort/group.

The "Z On Top" is the correct selection for the sort/group key in your case.

See the attached demo.

No doubt there are other ways, but this is a working model.

Joe ( 22.02K ): 5

Posted by: shellp Jan 10 2017, 08:53 PM

Thank you so much for the speedy response! I'll check this out and let you know!

Posted by: RJD Jan 10 2017, 09:01 PM

In case the DCount slows things down too much in the first demo, see this second demo with another approach to creating the sort/group key. It uses a separate query to get the city counts, then combines that with the table in a query to create the sort/group key, and uses the same report approach.

It might be more efficient.

Joe ( 23.93K ): 7