I am a teacher and trying hard to save records of my students co-curriculum achievements.
I've been trying to calculate the average of top two marks out of three marks of each of my students.
BUT How do i tell Access to auto detect the two highest scores for that particular student?


From Table : StudentsMarks

ID | StudentName | Uniform | Club | Sports | AverageTwo
1 | Alia | 20 | 12 | 35 | (get from calculation)
2 | Vikram | 14 | 28 | 3 |(get from calculation)
3 | Farah | 17 | 23 | 29 |(get from calculation)

I want to make it like this
For Alia, the average of top two marks is
( 20 + 35 ) / 2 = AverageTwo

For Vikram, the average of top two marks is
(14 + 28) / 2 = AverageTwo

For Vikram, the average of top two marks is
(23 + 29) / 2 = AverageTwo


Can anyone help me please. Thank you in advance.

---

Reply from Richard Rost:

Since your values are stored in a single table, you're going to have to use a complex IIF statement in a query to calculate this. Let's say you have three fields F1, F2, and F3. Your formula would be:

TopScore: IIF(AND(F1>F2,F1>F3),F1,IIF(AND(F2>F1,F2>F3),F2,F3))

That will calculate the TOP score. Now, all of my Access students want homework, so let's see who will be the first to calculate the NEXT highest score. It's a little bit trickier, but along the same lines.

Anyone...???