By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,510 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,510 IT Pros & Developers. It's quick & easy.

Sum of Top 3 Values in Query Result

P: n/a
Currently I am trying to write a program to score a gymnastics meet for four
events. Normally six competitors compete per event with the bottom 3 scores
per event being dropped and the top three being summed for an event score.
The total of the four event scores are the team score. Example below:

Team 1
======
Vault- 5, 6, 7, 8, 9, 10
EVENT SCORE: 10 + 9 + 8 = 27

Bars- 7, 7, 8, 9, 8, 9
EVENT SCORE: 8 + 9 + 9 = 26

Beam- 3, 4, 5, 9, 9, 9
EVENT SCORE: 9 + 9 + 10 = 28

Floor- 5, 6, 6, 6, 7, 8
EVENT SCORE: 8 + 7 + 6 = 21
Team 1 Team Score: 27 + 26 + 27 + 21 = 102

Team 2
======
Vault- 6, 7, 8, 9, 9, 9
EVENT SCORE: 9 + 9 + 9 = 27

Bars- 7, 7, 8, 8, 8, 8
EVENT SCORE: 8 + 8 + 8 = 24

Beam- 4, 5, 5, 8, 9, 9
EVENT SCORE: 9 + 9 + 8 = 26

Floor- 6, 7, 7, 7, 8, 9
EVENT SCORE: 9 + 8 + 7 = 24

Team 2 Team Score: 27 + 24 + 26 + 24 = 101

I need to have Access sum the top three values of the results of a query.
It would have to be grouped by team name and event. I would want the
results to show the Team Name, Event Total Score (top 3 scores summed). I
am assuming a separate query must be run to achieve the summation of the
four event scores to get a team score. See below for an example of what I
need. I want the results to look as follows from a Report:

Team Name Team Score

Team Vault Score
Team Bar Score
Team Beam Score
Team Floor Score

Your help on this would be greatly apprecaited. Please email me wtih
questions or suggestions. Thank you!

Matthew J. Freel
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Look into using a top n query, e.g. select top 3 blah...

(Note to Lyle, entire post snipped as subject says it all :-)
Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.