Top Values for Each Student

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mtech1@yadtel.net - 05 Feb 2006 17:43 GMT

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

Reply to this Message

Bob Quintal - 05 Feb 2006 18:18 GMT

> Access 2000
>
[quoted text clipped - 16 lines]
> 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.

Signature

Bob Quintal

PA is y I've altered my email address.

Reply to this Message