By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,192 Members | 1,783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,192 IT Pros & Developers. It's quick & easy.

Top Values for Each Student

P: n/a
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

Feb 5 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.
Feb 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.