mt****@yadtel.net wrote in
news:11**********************@o13g2000cwo.googlegr oups.com:
Access 2000
I need a query to return the Sum of the TOP 15 values for each
student.
This is to rank the students according to the sum of thier
best 15
scores. Each student could have any number of scores. Ex:
Dave has 55 scores, Mike has 24 scores, Sam has 12 scores,
....
I can get the Top 15 for the overall table, but I am having
trouble obtaining the above result.
I have a query that contans the student, P1,P2,P3,P4,P5,
RdTotal. P1-P5 are individual scores for the 5 parts of the
round and RdTotal is a calulated field showing the Sum of
P1-P5.
Thanks
Dale
It's not clear which field becomes the source for the top 15
scores. Is it rdTotal?
Anyways, the trick to doing something like you want is to
generate the sum of the top 15 using nested sub-queries.
Looks ugly, have not tested it, but this should work
SELECT DISTINCT x.student, (SELECT sum(rdTotal) from (SELECT top
15 y.rdtotal From scoretable as y WHERE y.RdTotal = x.rdtotal
ORDER BY y.RdTotal desc)) as SumTopScores from scoretable as x;
Build the inner query first, test, and then place in the outer
query.
--
Bob Quintal
PA is y I've altered my email address.
