Column Display with MIN & MAX

[Douglas J. Steele]

What do you want to happen if more than one student has the same score as
the Min or Max score?

 

[Syed Zeeshan Haider]

Hello Everybody,
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. How can I display both name and marks of the student with MIN
and MAX queries?

Thank you,

 

[Syed Zeeshan Haider]

I would like to display all of them.

What do you want to happen if more than one student has the same score as
the Min or Max score?

 

[Guest]

Try these two queries ---
Haider_1 ---
SELECT Min(Haider.Marks) AS MinOfMarks, Max(Haider.Marks) AS MaxOfMarks
FROM Haider;

Haider_2 ---
SELECT Haider_1.MinOfMarks, Haider.StudentID, Haider.Full_Name,
Haider_1.MaxOfMarks, Haider_2.StudentID, Haider_2.Full_Name
FROM (Haider_1 INNER JOIN Haider ON Haider_1.MinOfMarks = Haider.Marks)
INNER JOIN Haider AS Haider_2 ON Haider_1.MaxOfMarks = Haider_2.Marks;

--
KARL DEWEY
Build a little - Test a little

I would like to display all of them.