Query in VBA

[pietlinden]

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:


Use a TOP VALUES query. When you get it right, turn it into an append/
make table query and you're finished. All this stuff about VBA is a
red herring. The only snag would be that you will get ties if you use
top values. If you really have to have only 10 values, then you could
open a recordset based on a querydef (look up querydef in the help).
Then loop through the recordset it returns and add the values to a
table using AddNew.

 

[Syed Zeeshan Haider]

Hello Everybody,
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.

Anyways, above query is returning the ID (or primary key) from "Students"
table, instead of returning name of the student from "Marks" table?

Can someone figure out what am I missing here?

Thank you,