435,080 Members | 1,817 Online
Need help? Post your question and get tips & solutions from a community of 435,080 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