Hello,

I am trying to write a database for the science department at my school.

Pupils can sit module exams and these total up to their overall grade. Pupils can sit the same module more than once and so they can have more than one result for that module. I need to be able to select only the highest mark to do calculations with.

The results are stored in a table containing the following fields

resultID(Primary Key), StudentID(the students ID number), ExamID(the exam the student sat),exam session(the date that the exam was sat), Result(the mark the student got)

So, student X can take their biology exam in November and then try to improve it in March. I need to sum all of the students biology, chemistry and physics results but only the highest ones. Any help would be greatfully recieved.

Use the max value in query on the date column and group by student id

Use the max value in query on the date column and group by student id

Thank you for your reply.

Won't this will only give the latest date? I am trying to get the database to select only the highest result for an exam that the student sat. The student may have got the better result first time of sitting the paper.

Use Max() on the Result, group by student id and presumably, Exam ID.

You have to get max date for the student first and then use that date to get marks in a second query otherwise you will get wrong results

In my query I'm selecting a single student and then putting the max([result]) in the criteria for the result field and I get an error saying

"Cannot have aggregate functionin WHERE clause"

Where should I be asking the max () function?

use max on date field and group by on student id

In my query I'm selecting a single student and then putting the max([result]) in the criteria for the result field and I get an error saying

"Cannot have aggregate functionin WHERE clause"

Where should I be asking the max () function?

The Max() function goes in the main select clause:
select StudentID, ExamID, Max(Result)
from mytable
group by StudentID, ExamID;

If you want to know the last date a student sat for any exam, use khawar's suggestion.