Last week I asked how to produce a top 10 list of pupils based on thier number of merits and very helpfully the SQL which produced the desired result was
SELECT TOP 10 s.ID, s.Total, DCount("*","pmerits","Total > " & s.Total)+1 AS [Position]
FROM pmerits AS s
ORDER BY s.Total DESC;
I have just realised that this is giving me the top 10 in the school and what I want is the top 10 in each year group so I would need it grouped by year where year would be S1, S2 > S6
Can i just change the sql above or will I need to produce a query for each year group first.