Results 1 to 15 of 15

Thread: Top 6 Records

  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    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. #6
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

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

  8. #8
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    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. #10
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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. #12
    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. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote 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
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Oct 2004
    Posts
    16

    Thumbs up

    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. #15
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    It seems to me you would save yourself a lot of headaches by using the query summing stats by player and doing your grouping and aggregates at the report level. it would also lend itself well to reporting purposes...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •