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.