Trouble with my sorting and grouping

[Gordon]

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.

 

[Gordon]

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?

Thanks for your help.

Gordon

 

[Gordon]

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