Query in VBA

[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 borrowed 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 in SELECT TOP 2 (here
it
is 2) should be different every time 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,

 

[Ken Sheridan]

Are you saying you have the students' names as columns in the Marks table as
well as in the Students table? If so then you should not as it introduces
redundancy. You only need the StudentID column in Marks as a foreign key
referencing the primary key of Students.

Notwithstanding the above point, if I've understood correctly what you
want, the ten students with the highest total marks from all the exams (i.e.
the sum of a student's marks in the marks table), you should be able to do
this with a single query along these lines:

SELECT TOP 10 *
FROM
(SELECT Students.StudentID,
FirstName, LastName,
SUM(MarksCount) As TopMark
FROM Students INNER JOIN Marks
ON Marks.StudentID = Students.StudentID
GROUP BY StudentID, FirstName, LastName
ORDER BY SUM(MarksCount) DESC);

You can convert this to an 'append' query to insert the rows into your
Archives table:

INSERT INTO Archives
SELECT TOP 10 *
FROM
(SELECT Students.StudentID,
FirstName, LastName,
SUM(MarksCount) As TopMark
FROM Students INNER JOIN Marks
ON Marks.StudentID = Students.StudentID
GROUP BY StudentID, FirstName, LastName
ORDER BY SUM(MarksCount) DESC);

This assumes of course that table Archives has the same columns, both in
number and name, as those returned by the query, i.e. StudentID, FirstName,
LastName and TopMark in the above example. If the column names in Archives
differ then you'd need to include a comma separated list of the column names
in parentheses after the table name Archives at the end of the first line.

Note that its important that the above queries return the StudentID as well
as the names. Names can be duplicated, so if you returned only the names two
or more students with the same names would be treated as one student and all
their marks aggregated together. This scenario is not as uncommon as you
might think; I once worked in an office with two people with identical first
and last names, and recently I was at a clinic which was attended on the same
day by two people of the same gender not only with identical first and last
names, but also with the same date of birth. I've come across several
examples of the combination of names, gender and date of birth being used as
a key in medical systems; a very unwise practice.

Ken Sheridan
Stafford, England

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 borrowed 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 in SELECT TOP 2 (here
it
is 2) should be different every time 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,