how do I total up a query field if a different field matches

[Dave]

I would like to total up a field in a query only if a different field
matches. I have a query field named "TeamEventScore" and one called
"TeamNumber". I need to add all the "TeamEventScore" together if the
"TeamNumber" matches.

 

[John Vinson]

I would like to total up a field in a query only if a different field
matches. I have a query field named "TeamEventScore" and one called
"TeamNumber". I need to add all the "TeamEventScore" together if the
"TeamNumber" matches.

If TeamNumber matches.... what? What are you matching it against?
Surely you're not looking for records where the TeamNumber is the same
as the team's score?


John W. Vinson[MVP]

 

[Dave]

If TeamNumber matches.... what? What are you matching it against?
Surely you're not looking for records where the TeamNumber is the same
as the team's score?


John W. Vinson[MVP]
I have teams numbered 1-102 in a field. There are 2 or 3 people on a team that each have a seperate score but have the same team number. I would like it to add the scores of those 2 or 3 people together if their team number is the same.

 

[Smartin]

Sounds like a straightforward totals query?

Drag TeamNumber and TeamEventScore into a new query. Right click
somewhere under TeamEventScore and click "Totals". Change the total
property under TeamEventScore using the drop-down control to select Sum.
(The total property for TeamNumber should say Group By.)

Does this work?

 

[Dave]

Sounds like a straightforward totals query?

Drag TeamNumber and TeamEventScore into a new query. Right click
somewhere under TeamEventScore and click "Totals". Change the total
property under TeamEventScore using the drop-down control to select Sum.
(The total property for TeamNumber should say Group By.)

Does this work?

That worked for tabulating the totals. Now I am having trouble with the
report. I need the report to print the names of the people on a team but when
I add them to the report it wants to list the scores for each person
seperately and does not show the total even though I'm pulling the score from
the query you had me create..

 

[John Vinson]

That worked for tabulating the totals. Now I am having trouble with the
report. I need the report to print the names of the people on a team but when
I add them to the report it wants to list the scores for each person
seperately and does not show the total even though I'm pulling the score from
the query you had me create..

You should be able to use the Sorting and Grouping property of the
form to do this. Group by the team, and display the information on the
Team group header or footer. You can simply leave the Detail section
of the report empty, shrunk down to zero height.

John W. Vinson[MVP]

 

[Smartin]

That worked for tabulating the totals. Now I am having trouble with the
report. I need the report to print the names of the people on a team but when
I add them to the report it wants to list the scores for each person
seperately and does not show the total even though I'm pulling the score from
the query you had me create..

OK forget the totals query. Back to your original question, on the
report you will need grouping on TeamNumber. Add a text box to the
TeamNumber header. In its Data Control Source put something like
=SUM(TeamEventScore).

HTH

 

[Dave]

OK forget the totals query. Back to your original question, on the
report you will need grouping on TeamNumber. Add a text box to the
TeamNumber header. In its Data Control Source put something like
=SUM(TeamEventScore).

HTH

I can get everything to work except for the report. I get the report to work
but not in the order I want things. I want it to add up the scores and put
them in order from high to low. It calculates but if I go to sorting and
grouping I cannot move the team number, that it lists first in sort order,
because it changes the entire form and looses the calcualtions.

 

[tina]

try writing a Totals query with just the team number and score, GroupBy the
team number field and Sum the score field. then write a simple SELECT query
(you may have this one already done) on the same table, including the team
number, player name, and score fields. add the Totals query to the SELECT
query in design view, linking the query and the table on the common team
number field. that should give you a list of all players on all teams, with
the individual score of each player and also the team score, as

TeamNo Player Score TotalScore
1 A 5 20
1 B 8 20
1 C 7 20
2 D 3 9
2 E 6 9

in your report, Group on the team number field, and put the player and score
fields in the Detail section. put the TotalScore field in either the
GroupHeader or GroupFooter section, whatever suits you.

hth


a team that each have a seperate score but have the same team number. I
would like it to add the scores of those 2 or 3 people together if their
team number is the same.

 

[Dave]

I think I already have done that. I have the report how I want it and I do
have the total in the header like you say. If I move the total to the detail
it messes up the report. I am simply having trouble getting it to list them
in the proper order. When I go to Sorting and Grouping it lists the TeamName
first. It doesn't give me the option of sorting by the Sum of the scores and
if I change the TeamName so it is not first it changes the report.

 

[tina]

well, i'm not sure where your setup needs to be tweaked; in a simple test i
just wrote, i had no trouble sorting the team "groups" in the report by the
TeamScore field from the query. here's what i did; suggest you check the
following against your setup and see if you can spot the problem:

i started with a simple table, as
tblPlayerScores
playerID (primary key)
teamID (foreign key from a table listing all teams)
playerName
playerScore

i wrote the following Totals query, named qryTeamScores, as

SELECT teamID, Sum(playerScore) AS TeamScore
FROM tblPlayerScores
GROUP BY teamID;

i then wrote a SELECT query based on tblPlayerScores and the above Totals
query, and named it qryPlayerScores, as

SELECT tblPlayerScores.playerID, tblPlayerScores.teamID,
tblPlayerScores.playerName, tblPlayerScores.playerScore,
qryTeamScores.TeamScore
FROM tblPlayerScores LEFT JOIN qryTeamScores ON tblPlayerScores.teamID =
qryTeamScores.teamID;

i based the report on qryPlayerScores. in the report, controls bound to the
playerName and playerScore went into the Detail section. in the Sorting and
Grouping box, i sorted the TeamScore field Ascending, *without* making any
changes to the default settings in the GroupProperties. next, i sorted the
teamID field Ascending, AND set the GroupHeader and GroupFooter properties
to Yes. next, i sorted the playerScore field Descending, again *without*
making any changes to the default Group Properties settings. after closing
the Sorting and Grouping box, i added controls bound to the teamID and
TeamScore fields, to the teamID Header section.

in my test data, the team scores are 12, 11, and 25 for teams 1, 2, and 3
respectively. in my report, the team groups were properly sorted as team 2,
1, and then 3, per the settings in the Sorting and Grouping box.

hth