Xyfor said: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!!
Marshall Barton said: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.
--
Marsh
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?
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?
Marshall Barton said: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.
Marshall Barton said: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.
--
Marsh
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...
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?
Marshall Barton said: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.
Xyfor wrote:
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?
Xyfor wrote:
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.
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.
Marshall Barton said: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.
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
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.
Xyfor wrote:
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?
Xyfor wrote:
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.
Marshall Barton said: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
--
Marsh
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
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.
Marshall Barton said: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.
Xyfor wrote:
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
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.
Xyfor wrote:
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?
Xyfor wrote:
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.
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!
Marshall Barton said: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
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
the query's SQL view along with an explation of each field.
Xyfor wrote:
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
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.
Xyfor wrote:
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?
Xyfor wrote:
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.
Marshall Barton said: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.
--
Marsh
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
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!
Marshall Barton said: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
Xyfor wrote:
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
the query's SQL view along with an explation of each field.
Xyfor wrote:
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
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.
Xyfor wrote:
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?
Xyfor wrote:
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.