I've 4 tables such as this :

  • "Student" with fields ID, FullName, Gender, ClassID ( "ID" of "Class" ) ....

  • "Class" with fields ID, ClassName

  • "Subject" with fields ID, SubjectName

  • "Points" with fields ID, StudentID ( "ID" of "Student" ), SubjectID ( "ID of Subject" ), ExamPoint

The typical exam reason for students, is calculated with the addition of all of the point, then divide this result by the amount of subjects. If John has Maths 8 and British 4 and Literature 6 then his average exam point is (8+4+6)/3=6. If Mary has Maths 10 and British 5 then her average exam point is (10+5)/2=7.5

I am about choosing the one who has got the greatest average exam reason for each class then display their title, their class title as well as their average exam points. How do i get it done?

Try these 2 queries. The very first is a view that provides the average exam scores for every student.


SELECT  s.FullName, c.ClassName, AVG(p.ExamPoint) AS AvgPoints
FROM    Points p
INNER   JOIN Student s
        ON p.StudentID = s.ID
INNER   JOIN Class c
        ON s.ClassID = c.ID
GROUP   BY s.FullName, c.ClassName

The 2nd provides you with a look at the greatest earnings scores for every class.

SELECT  FullName, ClassName, AvgPoints
FROM    V_AveragePoints ap1
WHERE   AvgPoints = (SELECT MAX(AvgPoints) FROM V_AveragePoints WHERE ClassName = ap1.ClassName)

I authored these using standard T-SQL so that they should operate in MS Access, although I've not verified them using MS Access yet. They work fine in SQL Server, but tell me.