Top Values for Each Student

[mtech1@yadtel.net]

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

[Bob Quintal]

Re: Top Values for Each Student

mtech1@yadtel.n et wrote in
news:1139161417 .811066.154460@ o13g2000cwo.goo glegroups.com:
[color=blue]
> 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[/color]

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.