Results 1 to 6 of 6
  1. #1
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    15

    Top 10 list per each week looking at multiple years

    I am practicing Access and I thought fantasy football may be a fun project for it. I have multiple years of statistics and at one point kept them on separate tables/queries. I had created some top 5, 10, etc. lists of players for each week of play using SQL similar to this:



    Code:
    (SELECT top 5 qrySortPointsAllRB.Player, qrySortPointsAllRB.Week, qrySortPointsAllRB.PointsFROM qrySortPointsAllRB
    WHERE (((qrySortPointsAllRB.Week)=1))
    ORDER BY qrySortPointsAllRB.Points DESC)
    
    
    UNION ALL
    
    
    (SELECT top 5 qrySortPointsAllRB.Player, qrySortPointsAllRB.Week, qrySortPointsAllRB.Points
    FROM qrySortPointsAllRB
    WHERE (((qrySortPointsAllRB.Week)=2))
    ORDER BY qrySortPointsAllRB.Points DESC)
    
    
    UNION ALL
    
    
    (SELECT top 5 qrySortPointsAllRB.Player, qrySortPointsAllRB.Week, qrySortPointsAllRB.Points
    FROM qrySortPointsAllRB
    WHERE (((qrySortPointsAllRB.Week)=3))
    ORDER BY qrySortPointsAllRB.Points DESC)
    The code above continued for all 17 weeks. Now, I have multiple years of stats on one table/query, and I would like to create lists similar to what's above except that it will factor the different years. What changes would I need to make so that it would pull the top 5 players from each week in each qrySortPointsAllRB.Year as well?

  2. #2
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    15
    I immediately realized I can do a concatenate column between the weeks and years which will solve the issue... I'm still curious if there's a way to get where I was going through SQL, though.

  3. #3
    smatchymo is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2019
    Posts
    15
    Nevermind... My concatenate technique resulted in too complex of an SQL statement for Access to run. Too many unions going on when including three years of data.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How are your VBA skills?

    You could write the records from the queries to a tmp table (the records are temp, not the table).
    You could have a form to enter
    - the number of records (top 5, 10, etc.)
    - the number (or a range of) weeks
    - the years.

    The use VBA to create the append queries (in code), concatenating the parameters.
    Use the tmp table as the basis for queries for the form(s)/report(s).

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,
    as you posted this on the SQL server part of the forum, the easiest way is to solve this on the SQL server using windowing functions. Something like:

    Code:
    With qryRanks(Player, YearDate, Weekdate, Points, RankOrder) as (
    Select Player, DatePart(yy,Playdate), DatePart(ww, Playdate), Points, 
    dense_rank() over (partition by DatePart(yy, Playdate), DatePart(ww, Playdate) order by Points desc) as DenseRank from [MyTablename])
    select Player, YearDate, Weekdate, RankOrder
    from qryRanks
    where Rankorder <= 5
    order by yeardate, weekdate, rankorder

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Names Across Multiple Years
    By Corinne S in forum Queries
    Replies: 2
    Last Post: 08-02-2019, 11:34 AM
  2. Replies: 9
    Last Post: 06-19-2015, 03:37 PM
  3. Code to list 3 years record
    By buster497 in forum Access
    Replies: 5
    Last Post: 03-01-2014, 02:35 PM
  4. Design Question: Multiple years
    By dcfrancis in forum Database Design
    Replies: 4
    Last Post: 04-25-2012, 01:46 AM
  5. Query Multiple years
    By sammer021486 in forum Queries
    Replies: 3
    Last Post: 10-21-2009, 02:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums