A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Trouble with my sorting and grouping



 
 
Thread Tools Display Modes
  #1  
Old April 17th, 2010, 08:11 PM posted to microsoft.public.access.reports
Gordon[_2_]
external usenet poster
 
Posts: 37
Default Trouble with my sorting and grouping

I a using Access 2007. I have a report based on a query. In the
report I am using the inbuilt sorting & grouping feature to calculate
totals for each footer record (including percentages of the grand
total.)

This all works fine but the records are coming out in alpha order
(company names) - I want them to be sorted according to the count of
each record in descending order. Access will only let me select a
query field to do the sorting in the sorting and grouping box.

How do I achieve my objective?

Gordon.
  #2  
Old April 17th, 2010, 10:07 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Trouble with my sorting and grouping

Gordon wrote:

I a using Access 2007. I have a report based on a query. In the
report I am using the inbuilt sorting & grouping feature to calculate
totals for each footer record (including percentages of the grand
total.)

This all works fine but the records are coming out in alpha order
(company names) - I want them to be sorted according to the count of
each record in descending order. Access will only let me select a
query field to do the sorting in the sorting and grouping box.



You can not sort on a value that is calculated in the
report. The count calculation you want to sort by needs to
be done in the report's record source query.

If you want help with the query, please post an explanation
of what you are calculating and the fields involved.

--
Marsh
MVP [MS Access]
  #3  
Old April 19th, 2010, 06:37 PM posted to microsoft.public.access.reports
Gordon[_2_]
external usenet poster
 
Posts: 37
Default Trouble with my sorting and grouping

On Apr 17, 10:07*pm, Marshall Barton wrote:
Gordon wrote:
I a using Access 2007. *I have a report based on a query. *In the
report I am using the inbuilt sorting & grouping feature to calculate
totals for each footer record (including percentages of the grand
total.)


This all works fine but the records are coming out in alpha order
(company names) - I want them to be sorted according to the count of
each record *in descending order. Access will only let me select a
query field to do the sorting in the sorting and grouping box.


You can not sort on a value that is calculated in the
report. *The count calculation you want to sort by needs to
be done in the report's record source query.

If you want help with the query, please post an explanation
of what you are calculating and the fields involved.

--
Marsh
MVP [MS Access]


Thanks for replying Marshall. This gives me a problem. I know how to
design the query to do the count I was looking to do in the report but
if I do this aggregation in the query I lose a lot of the detail that
I was hoping to use in the report.. You see I have a form which I am
using to build the criteria for a WHERE clause which I then use to
open the report. As I say above, the control source for the report is
the query itself.

Can you suggest how I might overcome this dilemma. Is there an
alternative approach that I should take?

Thanks for your help.

Gordon
  #4  
Old April 19th, 2010, 08:01 PM posted to microsoft.public.access.reports
Marshall Barton
external usenet poster
 
Posts: 5,361
Default Trouble with my sorting and grouping

Gordon wrote:

On Apr 17, 10:07*pm, Marshall Barton wrote:
Gordon wrote:
I a using Access 2007. *I have a report based on a query. *In the
report I am using the inbuilt sorting & grouping feature to calculate
totals for each footer record (including percentages of the grand
total.)


This all works fine but the records are coming out in alpha order
(company names) - I want them to be sorted according to the count of
each record *in descending order. Access will only let me select a
query field to do the sorting in the sorting and grouping box.


You can not sort on a value that is calculated in the
report. *The count calculation you want to sort by needs to
be done in the report's record source query.

If you want help with the query, please post an explanation
of what you are calculating and the fields involved.


Thanks for replying Marshall. This gives me a problem. I know how to
design the query to do the count I was looking to do in the report but
if I do this aggregation in the query I lose a lot of the detail that
I was hoping to use in the report.. You see I have a form which I am
using to build the criteria for a WHERE clause which I then use to
open the report. As I say above, the control source for the report is
the query itself.

Can you suggest how I might overcome this dilemma. Is there an
alternative approach that I should take?



I don't know the details of what you are doing, but the
usual approach is to create another query that aggregates
the totals and then use another query to join that to the
table. The specifics about how you do that depend on your
grouping and filtering.

--
Marsh
MVP [MS Access]
  #5  
Old April 20th, 2010, 11:39 AM posted to microsoft.public.access.reports
Gordon[_2_]
external usenet poster
 
Posts: 37
Default Trouble with my sorting and grouping

On Apr 19, 8:01*pm, Marshall Barton wrote:
Gordon wrote:
On Apr 17, 10:07 pm, Marshall Barton wrote:
Gordon wrote:
I a using Access 2007. I have a report based on a query. In the
report I am using the inbuilt sorting & grouping feature to calculate
totals for each footer record (including percentages of the grand
total.)


This all works fine but the records are coming out in alpha order
(company names) - I want them to be sorted according to the count of
each record in descending order. Access will only let me select a
query field to do the sorting in the sorting and grouping box.


You can not sort on a value that is calculated in the
report. The count calculation you want to sort by needs to
be done in the report's record source query.


If you want help with the query, please post an explanation
of what you are calculating and the fields involved.


Thanks for replying Marshall. *This gives me a problem. *I know how to
design the query to do the count I was looking to do in the report but
if I do this aggregation in the query I lose a lot of the detail that
I was hoping to use in the report.. You see I have a form which I am
using to build the criteria for a WHERE clause which I then use to
open the report. As I say above, the control source for the report is
the query itself.


Can you suggest how I might overcome this dilemma. *Is there an
alternative approach that I should take?


I don't know the details of what you are doing, but the
usual approach is to create another query that aggregates
the totals and then use another query to join that to the
table. *The specifics about how you do that depend on your
grouping and filtering.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -


Thanks, I'll give that a try.

Gordon
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:14 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.