Selecting the two highest values among three fields

[JeepsR4Mud]

I'm teaching this term, and have put my grades and roster in Access. Each grading opportunity is a separate field.

Students will have to take 3 exams (Exam1, Exam2, and Exam3); however, their grade is based only on the 2 highest exam grades. There are other grades...FinalPaper, Attendance, Paper1, Paper2... the total of the grades is 600 points.

I can do the other calculations... what I do not know how to do is to select the two highest test values from the 3 Exam fields. I coudl do it manually, but I really prefer to simply enter grades, and then have the final grade calculated.

Thanks.

Gayle Ann

 

[grnzbra]

Each grading opportunity is a separate field.
Gayle Ann

That's the problem. There should be 2 fields. One field to accept the grade and one field to tell which grading opportunity it is. If you cannot re-do this, Create a Union query that stacks the three fields and then forces a value into a new field which will tell which grading opportunity it is. Then create a "Top x" query running from the union query and grouping on the new field.

 

[JeepsR4Mud]

I'm jsut setting it up now... but I'm not sure I understand your reply about a field for the opportunity, and a field to accept the grade. I won't know until the end of the term, which 2 of the three exam grades will be accepted. One student may use Exam1 and 2, and another 1 and 3, etc.

My fields are:

StudentID
Exam1
Exam2
Exam3
Attendance
Paper1
Paper2
Paper3
B
I thought about creating 3 new fields...say Exam1A, Exam2A, Exam3A... and using if, then, else statements? For example,

If exam1 >exam2 or exam3, then the value is Exam1, else the value is 0.

If exam2> exam1 AND exam3, then the value is Exam2, else the value is 0.

If exam3>exam1 AND exam2, then the value is Exam 3, else the value is 0

But, the above will only get the highest grade, not the middle grade. I could do that with:


If exam1 < exam2 and > exam3, then the value is Exam1, else the value is 0.

If exam2 < exam1 and > exam3, then the value is Exam2, else the value is 0.

If exam3< exam1 AND > exam2, then the value is Exam 3, else the value is 0

But, first of all, writing if then statements isn't my greatest strength, and creating 6 extra fields does nto seem to be the most efficient course of action... although, perhaps it is.

Gayle Ann

 

[boblarson]

What grnzbra was saying is that you would have an exam table something like this:

ExamID
StudentID
ExamNumber
ExamResults

Then, you would be able to use an aggregate query to pull the top 2 for each student ID.

If you store your results as a separate field for each exam, then it becomes more problematic (as you're discovering).