In my group header I have a Sum field where it sums all of the Money data in the details isection. How do I sort by this field. I want the report to display from highest value to lowest value, however in sorting and grouping box, it only allows me to choose from fields in in the query the report is based on. I want the report to be sorted by the sum field i have made int eh report.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Sorting By a Sum Field
- Thread starter czarjosh
- Start date
Since the Group Header is printed before the Detail, placing the Sum in the header won't get you anywhere. The records you're trying to sum haven't been read yet, at the time the header is being printed.
Access only sort the input. The output is based on the groupings but is still dependent on the input sequence for the order of the records.
If your Recordsource is simple, you could try either caculating the sum within the recordset, so you have the fields to sort on. Or, you could store the sums in an offline recordset as you calculate them, sort that recordset and then feed it back to the fields on the report.
Access only sort the input. The output is based on the groupings but is still dependent on the input sequence for the order of the records.
If your Recordsource is simple, you could try either caculating the sum within the recordset, so you have the fields to sort on. Or, you could store the sums in an offline recordset as you calculate them, sort that recordset and then feed it back to the fields on the report.
- Thread starter
- #3
Because you've grouped by company, then county, Access is going to list the detail records in that order. If you're just trying to get the companies to list in order of their sums, you'd be better off calculating those sums before you assign the recordsource of the report.
By that, I mean that you should either run a query to get these sums totally outside the report or via code in the Report_Open section. Then use the results of that query for the source of the report, rather than the original data set. since you'll already have the totals. you'll be able to specify that sum field in sort/grouping.
Although it probably doesn't apply for what you're doing now, you can specify calculated fields in the Sort/Grouping box. You just need to put an = before the calculation and the [] where needed. If Access can't determine the order of that calculation on a per record basis it will either give an error or you'll get a weird result. (Try it anyhow, you know you want to.)
---
You can either create it directly in ADO, or using Access you can create a table for this data. If you use a table, be certain to delete any records which may exist from your previous use of the table.
Store the $ amounts, along with any other fields you need, in each record of the table, as you read them in from the report. So, in your example, you would probably have 2 fields in the table: 1 field for the sum amount and 1 field for the company name associated with the sum.
After you've read in all the records, sort the table using the sums column. Then loop through the records in the table and print them out.
By that, I mean that you should either run a query to get these sums totally outside the report or via code in the Report_Open section. Then use the results of that query for the source of the report, rather than the original data set. since you'll already have the totals. you'll be able to specify that sum field in sort/grouping.
Although it probably doesn't apply for what you're doing now, you can specify calculated fields in the Sort/Grouping box. You just need to put an = before the calculation and the [] where needed. If Access can't determine the order of that calculation on a per record basis it will either give an error or you'll get a weird result. (Try it anyhow, you know you want to.)
---
You can either create it directly in ADO, or using Access you can create a table for this data. If you use a table, be certain to delete any records which may exist from your previous use of the table.
Store the $ amounts, along with any other fields you need, in each record of the table, as you read them in from the report. So, in your example, you would probably have 2 fields in the table: 1 field for the sum amount and 1 field for the company name associated with the sum.
After you've read in all the records, sort the table using the sums column. Then loop through the records in the table and print them out.
Create a query similar to your report's recordsource however group it by Company and sum the field that you want to sort by. Then, add this new totals query to your report's recordsource query and join the company fields. You will now have a "SumOf" field in your report's record source that you can use for sorting.
Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]
Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]
- Status
Similar threads
- Locked
- Question
