473,320 Members | 1,535 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Query to calculate Grade using Lookup and innerjoins.

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

[z{struck image... giving error}]

1.qryResults:Calculates the TotalAverage:(Maths+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:

Expand|Select|Wrap|Line Numbers
  1. SELECT SM.Student_ID
  2.    , SM.Students
  3.    , SM.TotalAverage
  4.    , SM.Maths
  5.    , SM.English
  6.    , SM.Science
  7.    , GD.Grade
  8. FROM qryResults 
  9.    AS SM 
  10.       INNER JOIN tblGrades 
  11.          AS GD 
  12.       ON (SM.TotalAverage>=GD.MinMarks) 
  13.          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)?
Attached Files
File Type: zip Grade.Awarding.zip (26.7 KB, 263 views)
Sep 6 '13 #1
3 4796
found 1 alternative method: but would like this one (using the SQL in the question) to work.
Alternative solution:
Expand|Select|Wrap|Line Numbers
  1. SELECT SM.Student_ID, SM.Students, SM.TotalAverage, SM.Maths, SM.English, SM.Science, (
  2.   SELECT GD.Grade
  3.   FROM tblGrades AS GD
  4.   WHERE GD.MinMarks =
  5.     (
  6.       SELECT TOP 1 GD.MinMarks
  7.       FROM tblGrades AS GD
  8.       WHERE GD.MinMarks <= SM.TotalAverage
  9.       ORDER BY GD.MinMarks DESC  
  10.     )
  11. ) AS Grade, (SELECT GD.Grade
  12.   FROM tblGrades AS GD
  13.   WHERE GD.MinMarks =
  14.     (
  15.       SELECT TOP 1 GD.MinMarks
  16.       FROM tblGrades AS GD
  17.       WHERE GD.MinMarks <= SM.Maths
  18.       ORDER BY GD.MinMarks DESC  
  19.     )
  20. ) AS MathsGrade, (SELECT GD.Grade
  21.   FROM tblGrades AS GD
  22.   WHERE GD.MinMarks =
  23.     (
  24.       SELECT TOP 1 GD.MinMarks
  25.       FROM tblGrades AS GD
  26.       WHERE GD.MinMarks <= SM.English
  27.       ORDER BY GD.MinMarks DESC  
  28.     )
  29. ) AS EnglishGrade, (SELECT GD.Grade
  30.   FROM tblGrades AS GD
  31.   WHERE GD.MinMarks =
  32.     (
  33.       SELECT TOP 1 GD.MinMarks
  34.       FROM tblGrades AS GD
  35.       WHERE GD.MinMarks <= SM.Science
  36.       ORDER BY GD.MinMarks DESC  
  37.     )
  38. ) AS ScienceGrade
  39. FROM qryResults AS SM;
  40.  
Sep 6 '13 #2
zmbd
5,501 Expert Mod 4TB
sashasandy
I can tell from your select queries that your database is not properly normalized.

Until you properly normalize your database you are going to continue to bang your head into the wall at 100MPH(161KPH).

Once again, I ask you to go back and refer to the tutorials and other information I've linked you to in your other threads.

Just so you will get the idea of what a properly normalized
database could look like:
NOW KEEP IN MIND
I've done this at 03h00cst, based on a database I made some 20+ years ago - from memory (and my spelling typing maybe a tad off after 20hrs-uptime)
In the following:
PK = Primary Key, always autonumber
FK = Foriegn Key to the table that follows, numeric long 1:M

In the first pass of thought as to how I would setup my tables if I were to be tracking mulitple students thru multiple classes over multiple years:

tbl_students
[students_pk]
[...student only information...] = for these type entries, I mean, keep the information strickly to the topic of the table. In this case, [students_campusid], [students_fname], [students_lname], [students_title], etc...

tbl_instructors
[instructors_pk]
[...instructors only information...]

tbl_courses
[courses_pk]
[...courses only information...] don't be adding classroom numbers or building or any other information that might change from term to term that would be in a differnt table...

tbl_gradescale
[gradescale_pk] autonumber
[gradescale_pointscale]
[gradescale_letterscale]
[gradescale_lowerPercentRange]
[...gradescale only information...]

tbl_classes
[classes_pk]
[classes_fk_instructors]
[classes_fk_courses]
[classes_cycle] (year or semester... might be FK to YAT)
[...Other realated information only...] such as room number.

tbl_enrollment
[enrollment_pk]
[enrollment_fk_classes]
[enrollment_fk_students]
(so for each class, there would be a record entry for each student, 20 classes, 15 students, = 300 records and so forth. Sounds like a pain, however, the form can be made very simple for the end user to make multiple entries for a student for a given [classes_cycle])

tbl_assignments
[assignments_pk]
[...assignment only information...] such as title, weighting of total grade, etc...

tbl_assesments
[assesments_pk]
[assesments_fk_enrollment]
[assesments_fk_assignment]
[assesments_pointscale] numeric long

--- For [assesments_pointscale]: In the FORM I would setup an UNBOUND combobox with Row source would be to a query based on tbl_gradescale pulling the point scale, letter scale and lower range. THe "after update event" would record the correct point scale grade to [assesments_pointscale]. This is because you need to keep the point, as given, for the future just incase the ranges shift in tbl_gradescale... unless you want this change to reflect for all students for all times for all courses in which case a simple lookup at the query level will work and the CBO can be a bound control.


Now you see what I've done here?

Let's say you want to find every grade a student has for last term...
Q_A) Find the PK for the student in tbl_students
Q_B) Find all the classes in tbl_classes for the given term
Q_C) From tbl_enrollment return only those record(s) that match records in Q_A and Q_B
Q_D) From tbl_assesments return only those records that also match against Q_C

Now you could have a running average of [tbl_assesments]![Assesments_pointscale] based on the records returned from Q_D... there are plenty of examples on how to do this on the net. You can add the weighting in, etc... once you have this... it should be a simple matter to convert [tbl_assesments]![Assesments_pointscale] to a letter grade by DLOOKUP() against "tbl_gradescale"

You should be able to figure out how to get a course, class, or instructor specific infromation from here.

Rabbit, NeoPa, or one of the profesional programmers that visit may be able to tweak what I've suggested to make things better.
Sep 7 '13 #3
zmbd, thanks for the concern, the database i used is only for demonstration purposes, instead of posting the entire thing i decided to break it down to something small and understandable.
any way. For future readers this query Calculates the GRADE,GPA and shows COMMENTS all per subject, and also the The Total and Average:
Cheers.
SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT SM.Student_ID, SM.Students, SM.class, SM.Maths, SM.English, SM.Science, (SELECT GD.Grade
  2.   FROM tblGrades AS GD
  3.   WHERE GD.MinMarks =
  4.     (
  5.       SELECT TOP 1 GD.MinMarks
  6.       FROM tblGrades AS GD
  7.       WHERE GD.MinMarks <= SM.TotalAverage
  8.       ORDER BY GD.MinMarks DESC  
  9.     )
  10. ) AS Grade, (SELECT GD.Grade 
  11.   FROM tblGrades AS GD
  12.   WHERE GD.MinMarks =
  13.     (
  14.       SELECT TOP 1 GD.MinMarks
  15.       FROM tblGrades AS GD
  16.       WHERE GD.MinMarks <= SM.Maths
  17.       ORDER BY GD.MinMarks DESC  
  18.     )
  19. ) AS MathsGrade, (SELECT GD.Grade 
  20.   FROM tblGrades AS GD
  21.   WHERE GD.MinMarks =
  22.     (
  23.       SELECT TOP 1 GD.MinMarks
  24.       FROM tblGrades AS GD
  25.       WHERE GD.MinMarks <= SM.English
  26.       ORDER BY GD.MinMarks DESC  
  27.     )
  28. ) AS EnglishGrade, (SELECT GD.Grade 
  29.   FROM tblGrades AS GD
  30.   WHERE GD.MinMarks =
  31.     (
  32.       SELECT TOP 1 GD.MinMarks
  33.       FROM tblGrades AS GD
  34.       WHERE GD.MinMarks <= SM.Science
  35.       ORDER BY GD.MinMarks DESC  
  36.     )
  37. ) 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
  38. FROM qryResults AS SM;
  39.  
Sep 8 '13 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Kimo R. | last post by:
Hello, Exim 4 integrated with MySQL 4. I have four tables: CREATE TABLE users ( uid bigint(10) unsigned DEFAULT '' NOT NULL auto_increment, listemail varchar(200) DEFAULT '' NOT NULL ,...
6
by: charliewest | last post by:
Can someone pls point me to or recommend the easiest way to calculate someone´s age using the TimeSpan object, in .NET CF? Isn´t there a simple way to use the TimeSpan object to calculate the...
6
by: Programador | last post by:
I'm getting this error when running this program: Cannot calculate rate using the arguments provided Module Module1 Sub Main() Rate(360, -694.44444444444446, 244274.69178082192) End Sub
3
by: jim.murphy | last post by:
I am not sure If I can do this with a lookup, but what I would like to do is perhaps use lookup to retrieve a control date from an unassociated table to control what date is entered in another...
0
by: northshore | last post by:
Hello, I am creating a windows application database. I have a primary table 'Individuals' and a lookup table 'Prefixes.' In the Individuals table, I have a column 'PrefixID' that references...
0
by: getkapilcs | last post by:
Hi, i want to query sql server using xml, where the required fields are to be specified in the xml dynamically. can you tell me how to generate such a xml file? regards, Kap
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: hanie | last post by:
a student wants to know his grade average for the semester. the grades are give in letter grades with numeric equivalents. develop a solution to calculate a grade average given the letter grades(the...
3
by: blamp | last post by:
I have completed most of the program I just cant get the letter grade to print from the void printGrade function.The output of the program should be: Line 1: Based on the course score, this...
8
by: hollinshead | last post by:
Hi there i have bit of an issue. i haver this database that is purely used for searching records under certain criteria. This criteria is chosen by the user on a form using list boxes and combo...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.