 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / January 2006How can you sort groups in an Access report?
I have a report that groups teams together. Each person within that team has a score which is subtotaled for a group total. How can I sort the teams by this subtotal? There has to be a way!! >I have a report that groups teams together. Each person within that team has >a score which is subtotaled for a group total. How can I sort the teams by >this subtotal? There has to be a way!! For a report to sort on a value, the value must be available in the report's record source query. Create a new query based om the report's current record source: SELECT T.*, DSum("score", "yourexistingquery", "team=" & T.team) As TeamTotal FROM yourexistingquery As T Noe you can set the report's Sorting and Grouping to sort on the TeamTotal field followed by grouping on the team field.  SignatureMarsh MVP [MS Access] Thanks for the response. I'm not sure where or how to set that expression up. Would I have to do this for each team? > >I have a report that groups teams together. Each person within that team has > >a score which is subtotaled for a group total. How can I sort the teams by [quoted text clipped - 13 lines] > Noe you can set the report's Sorting and Grouping to sort on > the TeamTotal field followed by grouping on the team field. As I tried to explain, that is not an expression. It is the report's new RecordSource query. And, no, you do not do it for each team, you do once for the report. If you do not know what a query is, click on the database window's Query tab, then click the New button. When the query design grid appears, use the View menu to switch to SQL view and Paste my example SQL statement over whatever is in the SQL window. Then check each name in my statement and change them to the names you are actually using. Once you think you have corrected my guesses at your names, try to run the query by switching to datasheet view. If it doesn't work, check the names more carefully until it does run. Then look at the data in the datasheet to make sure it is what you want. When the query is generating the desired dataset, close and save the query. Then open the report in design view and set its RecordSource property to the name of the query. Finally, open the report's Sorting and Grouping window (View menu) and specify the calculated field, TeamTotal, in the first Field/Expression row and the Team field in the second row. Close and save the report. Then preview it to check that results. SignatureMarsh MVP [MS Access] >Thanks for the response. I'm not sure where or how to set that expression >up. Would I have to do this for each team? [quoted text clipped - 16 lines] >> Noe you can set the report's Sorting and Grouping to sort on >> the TeamTotal field followed by grouping on the team field. Thanks again, and forgive my ignorance of SQL.... I'm not sure if I have the substitutions correct. Here is what I have so far... SELECT T.*, DSum("AGG","Rifle Team Totals Query", "team = " & T.team) As TeamTotal FROM [Rifle Team Totals Query] As T When this is run, it asks for a parameter value for T.team. If I enter one of the team names, I get an error stating: Access can't find the name 'team' you entered in the expression. When I click the OK button, I get a data sheet with the team total column, but no data.. What am I doing wrong? > As I tried to explain, that is not an expression. It is the > report's new RecordSource query. And, no, you do not do it [quoted text clipped - 41 lines] > >> Noe you can set the report's Sorting and Grouping to sort on > >> the TeamTotal field followed by grouping on the team field. Those messages imply that there is no such field as "team" in [Rifle Team Totals Query]. Please post a Copy/Paste of the query's SQL view along with an explation of each field. SignatureMarsh MVP [MS Access] >Thanks again, and forgive my ignorance of SQL.... I'm not sure if I have the >substitutions correct. Here is what I have so far... [quoted text clipped - 54 lines] >> >> Noe you can set the report's Sorting and Grouping to sort on >> >> the TeamTotal field followed by grouping on the team field. SELECT [Rifle Team Totals].[Team Name], [Rifle Team Totals].Rank, [Rifle Team Totals].[Last Name], [Rifle Team Totals].[First Name], [Rifle Team Totals].AGG FROM [Rifle Team Totals]; This is the rifle teams totals query. It will pull just this info from a table. Each shooter has an aggregate score which totaled together will make up the team aggregate score. I can do a sum in a report to get the team score, but I cant sort by that total. The report groups the teams and totals the scores but lists the teams alphabetically. I need to group the teams and sort by the team aggregate score. > Those messages imply that there is no such field as "team" > in [Rifle Team Totals Query]. Please post a Copy/Paste of [quoted text clipped - 57 lines] > >> >> Noe you can set the report's Sorting and Grouping to sort on > >> >> the TeamTotal field followed by grouping on the team field. That's good to know, but it's the other query that's having the problem. The error message tells me that you did not change my guess at a name, "team", to your actual field name ([Team Name]?) Try this: SELECT T.*, DSum("AGG","Rifle Team Totals Query", "[Team Name] = " & T.[Team Name]) As TeamTotal FROM [Rifle Team Totals Query] As T It looks like the [Team Name] field is a Text field. If that's the case, then it would be: SELECT T.*, DSum("AGG","Rifle Team Totals Query", "[Team Name] = """ & T.[Team Name] & """") As TeamTotal FROM [Rifle Team Totals Query] As T SignatureMarsh MVP [MS Access] >SELECT [Rifle Team Totals].[Team Name], [Rifle Team Totals].Rank, [Rifle Team >Totals].[Last Name], [Rifle Team Totals].[First Name], [Rifle Team Totals].AGG [quoted text clipped - 69 lines] >> >> >> Noe you can set the report's Sorting and Grouping to sort on >> >> >> the TeamTotal field followed by grouping on the team field. That worked! But on the report I dont need that column displayed for each individual, just for the team. Also when I turn the grouping on for the teams, the report still lists the teams alphabetically, not by the total score. I would like it to look something like this: "Team name" rank last name first name AGG SGT Smith Joe 185 PVT Jones Jim 200 PVT Doe John 193 PFC Doe Jane 205 Team Score: 783 In this format, ranked by team score. Thanks so much for your help already! > That's good to know, but it's the other query that's having > the problem. [quoted text clipped - 88 lines] > >> >> >> Noe you can set the report's Sorting and Grouping to sort on > >> >> >> the TeamTotal field followed by grouping on the team field. I think we've wandered around too long to remember this whole episode ;-) Set the top level Sorting and Grouping to sort on the TeamTotal field (the [Team Name] group is then in the second level) Move the TeamTotal text box from the detail section down to the group footer. SignatureMarsh MVP [MS Access] >That worked! But on the report I dont need that column displayed for each >individual, just for the team. Also when I turn the grouping on for the [quoted text clipped - 103 lines] >> >> >> >> Noe you can set the report's Sorting and Grouping to sort on >> >> >> >> the TeamTotal field followed by grouping on the team field. Thank you for all your help, Sir!! That turned out perfect!! > I think we've wandered around too long to remember this > whole episode ;-) [quoted text clipped - 112 lines] > >> >> >> >> Noe you can set the report's Sorting and Grouping to sort on > >> >> >> >> the TeamTotal field followed by grouping on the team field. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.