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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|