Summarizing using the TOP command

[Dennis Snelgrove]

I've got a database for a yearly competition wherein there are a number of
events, each of which has a score attached. As part of the end-of-contest
summarizing, I've got to generate a report based on team totals. I could do
this easily enough with an aggregate query, except for the fact that each
team could have a different number of members. The logic applied to make it
fair is to find out the size of the smallest team, then take the highest
scores from each team for the same number of people. For example, if the
smallest team has 4 members, then for each team we need to add up the 4
largest scores. I must be missing something, because I can't for the life of
me come up with the SQL to achieve this.

My tables look like this:

tblPlayer
PlayerID - autonumber PK
FirstName
LastName
Team

tblEvent
EventID - autonumber PK
EventName

tblPlayer_Event_Intersect
PlayerID FK
EventID FK
Score
(The PK is a combination of PlayerID and EventID)

Any insights or suggestions are really appreciated.

 

[Jeff Boyce]

Dennis

Given that a larger team could have more higher (and lower) scores than your
"base" (4 member) team, taking the 4 largest scores penalizes your smaller
teams. Since you are taking the larger scores, the fact that a big team has
some lower scores is irrelevant.

Wouldn't an "average" team score better reflect each team's performance?

Good luck

Jeff Boyce
<Access MVP>

 

[Dennis Snelgrove]

You may be right, Jeff, except that this is the way the group wants it done.
I came into this about 10 years after they started this annual event, and I
don't think that they're going to change it now. You know the mantra...
"Mine is not to question why. Mine is to program or die.'

 

[Jeff Boyce]

Dennis

Feel free to pass this former statistics instructor's observations along to
the group. What they are insisting on encourages large teams, and
misrepresents team scores.

If "team scores" are desired, maybe your data needs a new table of "team",
with another table for "team-member"?

Good luck

Jeff Boyce
<Access MVP>