Wakwehu Wakwehu - 1 year ago 103
SQL Question

Select TOP predicate

I have a table with fields StudentID, ClassID, ExamID, SubjectID and Scores
I am trying to get a sum of 7 top Scores from attempted subjects from every student. The SQL statement below is giving me the sum of scores of all the subject from the top 7 students:

SELECT TOP 7 Sum(tblScores.Scores) AS Total, tblScores.AdmissionID
FROM tblScores
WHERE (((tblScores.ExamID)=[Forms]![frmReports]![lstC]) AND ((tblScores.ClassID)=[Forms]![frmReports]![lstB]))
GROUP BY tblScores.AdmissionID
ORDER BY Sum(tblScores.Scores) DESC;

The Class and Exam criteria is read from the Form "frmReports"

Any one who can help me out?

Answer Source

@ Parfait - Thank you for your guidance. I have actually tweaked your solution to get a perfect answer: Here is the sql:

SELECT Dupe.AdmissionID, 
     (Select Sum(qryFilteredScores.Scores) From qryFilteredScores 
      Where ([AdmissionID]=Dupe.[AdmissionID] 
      AND ((qryFilteredScores.ClassID)=[Forms]![frmReports]![lstB]) 
      AND ((tblScores.ExamID)=[Forms]![frmReports]![lstC]))) AS Total
FROM qryFilteredScores AS Dupe
GROUP BY Dupe.AdmissionID
ORDER BY Dupe.AdmissionID;

Alternatively, you can use a derived table or stored query (where you save derived table as separate object referenced in this query) and avoid inline subqueries:

SELECT Dupe.AdmissionID, Total.TotalScore
FROM qryFilteredScores AS Dupe
     (SELECT sub.AdmissionID, Sum(sub.Scores) As TotalScore
      FROM qryFilteredScores sub
      WHERE ([AdmissionID]=Dupe.[AdmissionID] 
      AND ((sub.ClassID)=[Forms]![frmReports]![lstB]) 
      AND ((tblScores.ExamID)=[Forms]![frmReports]![lstC]))
      GROUP BY sub.AdmissionID) AS Total
ON Dupe.AdmissionID = Total.AdmissionID 
GROUP BY Dupe.AdmissionID, Total.TotalScore
ORDER BY Dupe.AdmissionID;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download