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:
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?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)