# Thread: Top 6 Records

1. Registered User
Join Date
Oct 2004
Posts
16

## Unanswered: Top 6 Records

Hi I have a hockey pool I am running. Each participant has selected a team that consists of 7 Players, however only their top 6 players (based on point production) count toward their point sum.

Table Picks:
IDteam (text)
IDplayer (pri text)
IDpos (text)
RoundID (#)
DraftID (auto #)

Table NHLstats:
ID (#)
Pos (text)
team (text)
Player (text)
GP (#)
G (#)
A (#)
P (#)

So each participant (idteam) in picks consists of 7 picks. The pool winner is the participant (idteam) that has the highest sum of points for their top 6 players. I have a hard time explaining this. I hope you can understand.
So far I have this, which totals all 7 of their players.

Code:
SELECT DISTINCTROW Picks.IDTeam, Sum(nhlstats.GP) AS [Sum Of GP], Sum(nhlstats.G) AS [Sum Of G], Sum(nhlstats.A) AS [Sum Of A], Sum(nhlstats.PTS) AS [Sum Of PTS]
FROM Picks INNER JOIN nhlstats ON Picks.IDplayer = nhlstats.player
GROUP BY Picks.IDTeam
ORDER BY Sum(nhlstats.PTS) DESC;
Thanks for looking.

2. Registered User
Join Date
Apr 2004
Location
metro Detroit
Posts
634
You can do this in the query properties (from design view) by putting a 6 in the 'Top Values' field and sorting by the points field.

3. Registered User
Join Date
Oct 2004
Posts
16
Thank you, I have tried to do this but then it only gives me the top 6 team totals, rather than the top 6 players from each team. Is this because all the other columns are sums?

4. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
Try this link from Microsoft:

http://support.microsoft.com/default...b;en-us;210039

The first method is the one you want. Also be sure to include a unique ID for each player. That way if two players on the same team have the same points you won't get both of them.

5. Registered User
Join Date
Oct 2004
Posts
16
Thanks that seems to be exactly what I want to do. I followed that method and got this:

Code:
SELECT Picks.IDTeam, Picks.IDplayer, Sum(nhlstats.PTS) AS SumOfPTS
FROM Picks INNER JOIN nhlstats ON Picks.IDplayer = nhlstats.player
GROUP BY Picks.IDTeam, Picks.IDplayer
HAVING (((Sum(nhlstats.PTS)) In (Select Top 3 [PTS] from Picks Where [IDplayer]=[NHLstats].[player] order by [PTS] Desc)))
ORDER BY Picks.IDTeam, Sum(nhlstats.PTS) DESC;
when I execute the sql I get this error:

"You tried to execute a query that does not include the specified expression "IDteam" as part of an aggregate function."

Not sure how to correct this.

6. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Good call on that solution DC.

Are you sure that is the message? Are you sure it wasn't IDplayer? Coz that is missing from the group by.
Last edited by pootle flump; 10-24-05 at 04:36.

7. Registered User
Join Date
Oct 2004
Posts
16
I just ran it again to be sure. Definitly IDteam.

8. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Whoops - misread your query - apols. I think the below is what you are looking for:

Code:
SELECT Picks.IDTeam, Picks.IDPlayer, Sum(nhlstats.Pts) AS SumOfPts
FROM Picks INNER JOIN nhlstats ON Picks.IDPlayer = nhlstats.Player
GROUP BY Picks.IDTeam, Picks.IDPlayer
HAVING Picks.IDPlayer In (SELECT TOP 6 PLayer FROM nhlStats C INNER JOIN Picks D ON C.Player = D.IDPlayer WHERE D.IDTeam = Picks.IDTeam group by PLayer ORDER BY sum([Pts]) Desc, Player)
ORDER BY Picks.IDTeam, Sum(nhlstats.Pts) DESC
The
Code:
ORDER BY sum([Pts]) Desc, Player
is to handle the possibility of the sixth and seventh lowest scorers tying. In this case, Access returns both (i.e. TOP 7). If you include the players ID in the order by it will only pick one (in this case the one with the lowest player ID).

HTH
Last edited by pootle flump; 10-24-05 at 05:02.

9. Registered User
Join Date
Oct 2004
Posts
16
Thats Fantastic! thank you for the help. One last question if you dont mind? When I run this I get a list of the top six players from each team. Is there a way to show the sum of the six players?

10. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
it is indeed. You need to remove the PlayerID from the select and group by. As such, the having clause becomes invalid so you need to change this to a where.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Unless your homework expressly requires you to do it by query, Id suggest keeping the one query and then using report or form footers to do the summation.

12. Registered User
Join Date
Oct 2004
Posts
16
Thank Pootle, I actually just made another query of the query and that worked for my purposes. I will try out what you suggested also. Thanks for all your help.

Healdem - where did I say this was homework? Thanks for your suggestions anyway.

13. King of Understatement
Join Date
Feb 2004
Location
One Flump in One Place
Posts
14,912
Originally Posted by SevenEleven
Thank Pootle, I actually just made another query of the query and that worked for my purposes. I will try out what you suggested also. Thanks for all your help.

Healdem - where did I say this was homework? Thanks for your suggestions anyway.
Ah - you didn't fall for Healdems elaborate trap eh? We get lots of homework Qs here and - to be fair - it does sound like one. However, I am fairly confident you aren't a student (and are deserving of help anyway even if you are) as you clearly made a good couple of decent stabs at the problem yourself - not, sadly, a common trait amongst todays young acdemics *sigh*

Anyhoo - selecting N records per group is an age old (and by age old, of course, I mean since the advent of relational databases) problem so please - post your other solution too for posterity

14. Registered User
Join Date
Oct 2004
Posts
16
Lol I never had computer class in HS so I have no idea what a question would sound like

Here is the query that I made to Query the one you made for me in post #8.

Code:
SELECT DISTINCTROW Query1.IDTeam, Sum(Query1.SumOfPts) AS [Sum Of SumOfPts], Sum(Query1.SumOfGP) AS [Sum Of SumOfGP], Sum(Query1.SumOfG) AS [Sum Of SumOfG], Sum(Query1.SumOfA) AS [Sum Of SumOfA]
FROM Query1
GROUP BY Query1.IDTeam
ORDER BY Sum(Query1.SumOfPts) DESC;
The code you created being Query1 of course. This gives me the sum of the top 6 players on each team.

Thanks again!

15. Purveyor of Discontent
Join Date
Mar 2003
Location
The Bottom of The Barrel
Posts
6,102