How can I display both name and marks of the student with MIN and MAX queries?

Question

In Access 2003, I have a table with two columns, in addition to the primary key ID columns. First column contains students' names and second column contains students marks. I am trying to create a query which would display name and marks highest scoring student. When I use MAX or MIN (for lowest scoring student) query displays only marks, and does not display the students name.

select max(marks) displays only marks. I want to display corresponding name too.

Answer 1

If I read your question correctly you want the name and the mark for only the top (or bottom) scoring student. If that's the case you can use a subquery to get the result(s) you want:

SELECT StudentName, Mark FROM Table WHERE Mark =(SELECT MAX(mark) FROM Table)

Answer 2

You need to include the Name column, in your query.

Answer 3

Isn't there mix() and max() in SQL? Put yourself in SQL mode within Access this is simple query.

select max(marks) from Table

Answer 4

you want to go to the microsoft office help page & search there

cheers!