Re: Top Values for Each Student

mtech1@xxxxxxxxxx wrote in
news:1139161417.811066.154460@xxxxxxxxxxxxxxxxxxxxxxxxxxxx:

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.
.

Relevant Pages

  • Re: find total based on 2 criteria
    ... Like one query does the Sum, then link that to a query with the discounts. ... > someone owes for a student tenancy database and use this result as the ... > The students table holds records on the student and the rentpayment table ...
    (microsoft.public.access.queries)
  • RE: total points then print those that are over a threshold
    ... HAVING Sum([IncidentPoints])>3) ... will not run the query. ... Select Sum, FieldName2 From TableName ... student uses their phone or violates a dress code, ...
    (microsoft.public.access.queries)
  • Re: Aggregate Queries hassles
    ... I'll try using the Query By Example tool first. ... > some samples of the data for, say, one student, could be most helpful. ... >>the sum of all the scores times the group weight times the activity ... >>group's weight needs to first be divided by the sum of all the group ...
    (microsoft.public.access.queries)
  • Top Values for Each Student
    ... I need a query to return the Sum of the TOP 15 values for each student. ... P1-P5 are individual scores for the 5 parts of the round and RdTotal is ...
    (comp.databases.ms-access)
  • Re: Cumulative cost
    ... When you say "running total per student", I believe this means the same ... You can use a Totals query to get a "per student" Sum ... AND calculate something you're calling "cumulative cost". ...
    (microsoft.public.access.forms)