# Top 10 list per each week looking at multiple years

1. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2019
Posts
14

## 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. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2019
Posts
14
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. Novice
Windows 7 64bit Access 2010 64bit
Join Date
Jun 2019
Posts
14
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. Master of Nothing
Windows 7 32bit Access 2010 32bit
Join Date
Sep 2010
Location
Posts
8,615

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. 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```

#### Posting Permissions

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