Query to calculate Grade using Lookup and innerjoins.

[SashaSandy]

I have a database with:
2 tables, tblStudentMarks and tblGrades
2 Queries qryResults and qryStudents_Grades




1.qryResults:Calculates the TotalAverageMaths+English+Science)/3.
2.qryStudents_Grades:Calculates the students grades based on the TotalAverage by comparing the total average to the min & max marks on the tblGrades.


Grades are compared within a range specified in tblGrades with the following SQL wich is the query qryStudents_Grades:

SELECT SM.Student_ID, SM.Students, SM.TotalAverage, SM.Maths, SM.English, SM.Science, GD.Grade
FROM qryResults AS SM INNER JOIN tblGrades AS GD ON (SM.TotalAverage>=GD.MinMarks) AND (SM.TotalAverage<=GD.MaxMarks);

Question: i would like to also calculate the grades of the individual subjects in the same query (qryStudents_Grades), where the marks got by a student in the subject..say maths is compared to the (tblGrades) and grade is displayed in a colum say maths_grades for each student.
How can i do this for all subjects in one query. (qryStudents_Grades)?

[June7]

You can use DLookup.

[SashaSandy]

this worked for me, Query SQL statement. however, I would like the one i posted in the question to work as well.

SELECT SM.Student_ID, SM.Students, SM.TotalAverage, 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
FROM qryResults AS SM;

[June7]

You mean like:

SELECT DISTINCT *
FROM (SELECT Student_ID, Students, TotalAverage, Maths, English, Science, (SELECT GD.Grade
  FROM tblGrades AS GD
  WHERE GD.MinMarks =
    (
      SELECT TOP 1 GD.MinMarks
      FROM tblGrades AS GD
      WHERE GD.MinMarks <= 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 <= 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 <= 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 <= Science
      ORDER BY GD.MinMarks DESC  
    )
) AS ScienceGrade
FROM qryResults) AS SM INNER JOIN tblGrades ON (SM.TotalAverage>=tblGrades.MinMarks) AND (SM.TotalAverage<=tblGrades.MaxMarks);

[SashaSandy]

Hallo! june7, Thanks for your reply, YES! your solution does part of the trick as mine...i will be more elaborate now.

the database basically has to calculate the individual subjects grades and Equivalent GPA marks for the subjects then a Total Average for the Grades and GPA + show comments.
the tblGrades has the fields: Grade(A,A-,B..etc),MinMarks,MaxMarks,MinGPA,MaxGPA and comment(good,Excellent...etc).
the tblStudentMarks is where the subject marks are inputted
the qryResults is where the Totalmarks is calculated (to be used to get the Average grade and Average GPA)
the qryStudents_Grades is where (My problem comes in). i need THIS query to get the Grade,GPA and comment for individual subjects and then for the Average.

am sorry i have posted pretty-much way outside what i wanted but i hope you get the clear picture now, i have tried numerous SQL statement for that particular query but i keep getting half results. i would really appreciate your help.

[June7]

I thought the query I provided does show the letter grade for the average? See the field named SM.Grade.

What 'comment'?

What is your formula for GPA? Don't you need points assignment (A=4, B=3, C=2, D=1, F=0) and course credit hours?

[SashaSandy]

June7 Yes it did, thanks but this is what i wanted.

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;

[June7]

Issue solved?