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.