Query is returning the ID from "Students" table, instead of returning name of the student from "Marks" table.

In Access 2003, I have two tables i.e. Students and Marks.


Students is simply the list of students while Marks is the list of total marks each student earned in a certain exam. Marks table uses the names from the Students table. In Marks, I have a field called StudentID (with Number Data Type) which retrieves the names of the students from Students table using their numerical primary key (I copied this technique


from Northwind.mdb sample file).


I have written a VBA procedure which should pick top ten students based on their marks from Marks table and put their names in another table called Archives. In my VBA code I have put following SQL code:





SELECT MIN(Topper) AS Second FROM (SELECT TOP 2 [Marks].StudentID AS Topper FROM [Marks] ORDER BY [Marks].MarksCount DESC);





This SQL chunk should be run 10 times and the number SELECT TOP 2 (here it is 2) should be different as 1 should return highest scorer student, 2 should return second highest and so forth.


What am I missing here?

Query is returning the ID from "Students" table, instead of returning name of the student from "Marks" table.
Assuming MarksCount is the field that contains the sum, and that the primary and foriegn keys are both named StudentID:





SELECT TOP 10 m.MarksCount, s.StudentName


FROM Marks AS m


LEFT JOIN Students AS s ON m.StudentID = s.StudentID


ORDER BY m.MarksCount DESC