Min function

  • Thread starter Thread starter David
  • Start date Start date
D

David

I would like to determine the two lowest values in a calculated field in a
cross-tab query. I know that using the "min" function will return the
lowest value, but how can the second lowest value be obtained?

***********************
For example, a teacher gives her students 10 quizzes during the semester and
drops each student's two lowest scores.

If the teacher dropped only the lowest quiz, the formula for the semester
quiz grade would be: "(Sum([QuizScore])-Min([QuizScore]))/9"

How could a formula be constructed in a calculated query field to subtract
each student's two lowest scores???

Thanks,

David
 
David,

I don't think I have enough information about your actual data to give
a specific response. But I think one solution would involve sorting
the QuizScores in ascending order, and then using the TOP 2 predicate
to isolate the scores for each student which should be excluded (or
perhaps sort descending and use TOP 8?)

- Steve Schapel, Microsoft Access MVP
 
Back
Top