Query to show grade,GPA and comment at once.

[matiyas]

Hallo!
i have the following database with:
-tblGrades-contains: gradeID,Grade,MaxGrade,MinGrade,GPA and comment
-tblStudents_marks:StudentId,name and marks in various subjects

-qryResults: calculates totals and average in all subjects
-qryGrades: Grades the students as per marks and also the Average grade.
(i know the database is not Normalized,only using to briefly explain)

qryGrades: calculates the individual subject grades and average grade by comparing the marks in the subjects to tblGrades. However i would like to create an all in 1 query to get the GPA and Comment as well alongside the grade, without making different queries to do that.
SQL for the query is:

SELECT SM.Student_ID, SM.Students, SM.class, SM.Maths, SM.English, SM.Science, (SELECT GD.Grade
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.TotalAverage
      ORDER BY GD.MinMarks DESC  
    )
) AS Grade, (SELECT GD.Grade 
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.Maths
      ORDER BY GD.MinMarks DESC  
    )
) AS MathsGrade, (SELECT GD.Grade 
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.English
      ORDER BY GD.MinMarks DESC  
    )
) AS EnglishGrade, (SELECT GD.Grade 
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.Science
      ORDER BY GD.MinMarks DESC  
    )
) AS ScienceGrade, SM.TotalAverage
FROM qryResults AS SM;

 

[matiyas]

the working SOLUTION.

SELECT SM.Student_ID, SM.Students, SM.class, SM.Maths, SM.English, SM.Science, (SELECT GD.Grade
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.TotalAverage
      ORDER BY GD.MinMarks DESC  
    )
) AS Grade, (SELECT GD.Grade 
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.Maths
      ORDER BY GD.MinMarks DESC  
    )
) AS MathsGrade, (SELECT GD.Grade 
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.English
      ORDER BY GD.MinMarks DESC  
    )
) AS EnglishGrade, (SELECT GD.Grade 
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= SM.Science
      ORDER BY GD.MinMarks DESC  
    )
) AS ScienceGrade, SM.TotalAverage, (SELECT GD.GPA FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.Maths ORDER BY GD.MinMarks DESC)) AS MathsGPA, (SELECT GD.GPA FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.English ORDER BY GD.MinMarks DESC)) AS EnglishGPA, (SELECT GD.GPA FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.Science ORDER BY GD.MinMarks DESC)) AS ScienceGPA, (([MathsGPA]+[EnglishGPA]+[ScienceGPA])/3) AS TotalGPA, (SELECT GD.Comment FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.Maths ORDER BY GD.MinMarks DESC)) AS MathsComment, (SELECT GD.Comment FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.English ORDER BY GD.MinMarks DESC)) AS EnglishComment, (SELECT GD.Comment FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.Science ORDER BY GD.MinMarks DESC)) AS Sciencecomment, (SELECT GD.Comment FROM tblGrades AS GD WHERE GD.MinMarks =(SELECT TOP 1 GD.MinMarks FROM tblGrades AS GD WHERE GD.MinMarks <= SM.TotalAverage ORDER BY GD.MinMarks DESC)) AS Totalmarkscomment, ([Maths]+[English]+[Science]) AS Totals
FROM qryResults AS SM;