Prev: Grouping not working right
Next: show total in each page , How
From: Gordon on 17 Apr 2010 15:11
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.
From: Marshall Barton on 17 Apr 2010 17:07
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]
From: Gordon on 19 Apr 2010 13:37
On Apr 17, 10:07 pm, Marshall Barton <marshbar...(a)wowway.com> 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
From: Marshall Barton on 19 Apr 2010 15:01
Gordon wrote:

>On Apr 17, 10:07�pm, Marshall Barton <marshbar...(a)wowway.com> 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]
From: Gordon on 20 Apr 2010 06:39
On Apr 19, 8:01 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> Gordon wrote:
> >On Apr 17, 10:07 pm, Marshall Barton <marshbar...(a)wowway.com> 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
 | 
Pages: 1
Prev: Grouping not working right
Next: show total in each page , How