UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sort On Volumes Per Group?, Access 2010    
 
   
shellp
post Jan 10 2017, 08:09 PM
Post#1



Posts: 36
Joined: 4-July 11



Hello

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.
Go to the top of the page
 
RJD
post Jan 10 2017, 08:42 PM
Post#2


UtterAccess VIP
Posts: 7,060
Joined: 25-October 10
From: Gulf South USA


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.

HTH
Joe
Attached File(s)
Attached File  SortOnVolumesPerGroup.zip ( 22.02K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
shellp
post Jan 10 2017, 08:53 PM
Post#3



Posts: 36
Joined: 4-July 11



Thank you so much for the speedy response! I'll check this out and let you know!
Go to the top of the page
 
RJD
post Jan 10 2017, 09:01 PM
Post#4


UtterAccess VIP
Posts: 7,060
Joined: 25-October 10
From: Gulf South USA


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.

HTH
Joe
Attached File(s)
Attached File  SortOnVolumesPerGroup_Rev1.zip ( 23.93K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st January 2017 - 03:27 AM