Counting courses and calculating GPA

[sherikream]

I need to do the following in MS Access. Any help would be greatly appreciated.

Situation:

A student is required to have earned credit for 10 specfific courses and there is a average GPA requirement for 8 of the 10 courses. Fs or failing equivalent grades are to be calculated in the GPA, if any. Courses that earned transfer credit (CR) without a grade are to be counted for credit but not calculated in GPA.

I have 2 tables, one of student id's and the courses they've passed or failed out of the 10 including grade, grade points, credits earned for the course, semester taken. The 2nd table is student major, name, email, etc.

Sample data: (also attached if not clear below)
Total 11 courses because one was repeated.

Course Grade Grade Points Credits
Course1 A 4 3 credits
Course2 B 3 3 credits
Course3 C 2 3 credits
Course4 D 1 3 credits
Course5 F (failed) 0 0 credits
Course6 A 4 3 credits
Course7 CR (credit) CR 3 credits
Course8 C 2 3 credits
Course9 D 1 3 credits
Course10 C 2 3 credits
Course11 D 1 3 credits


1. How do I go about getting count of total courses each student passed or earned credit, not including failed courses in the counting as it's only relevant in GPA calculation.

2. Of students who have passed all 10 courses, how do I calculate GPA for only 8 specific courses including Fs.

I'm not sure if any additional info might be needed. If any additional information is needed, I'm ready to provide.

Thank you all....in advance.


I forgot to mention that i'm using Windows 10 and Microsoft 2016.

 

[JHB]

..

I'm not sure if any additional info might be needed. If any additional information is needed, I'm ready to provide.
..

You need to include the result you want for question 1 and 2 and also why the result is what is.
When we know that I'm sure we can provide you some solution.
What does: GPA, FS and failing equivalent grades, mean?

 

[Insane_ai]

I created a table to emulate what you describe then queried it the best I can based on your criteria.

I eliminated the specific student data as you can do this with a simple join in design mode.

The Courses table looks like this: (Quick and dirty)
StudentID Integer, indexed
CourseName Text
Grade Text
GradePoints Integer
CreditsEarned Integer

SQL to extract the basic information you are requesting.

SELECT Courses.StudentID
     , Count(Courses.CourseName) AS CoursesPassed
     , Sum(Courses.CreditsEarned) AS CreditsEarned
     , Sum(Courses.GradePoints) AS GradePoints
     , Avg([Courses]![GradePoints]) AS GPA, CC.CoursesTaken

FROM Courses INNER JOIN 
[SELECT Count(Courses.CourseName) as CoursesTaken
     , StudentID
FROM Courses
GROUP BY Courses.StudentID]. AS CC ON Courses.StudentID = CC.StudentID

WHERE (((Courses.Grade)<>"F"))

GROUP BY Courses.StudentID, CC.CoursesTaken;

Adjust this to match your table and column names and join your students table to fill in the details you want.

I hope this example helps you get where you're wanting to go for part 1.

Part two:

Write a select query to choose all the students who have passed all 10 classes with the details of those classes included to choose the 8 in another query.
Then write another query limiting the gpa calculation only those 8.
SELECT * from MyQuery Where CourseName IN(Course1, Course2, Course3, Course4 ...etc)

 

[sherikream]

[FONT=&quot]Correction, Credits for Course5 F should be 3 credits and not 0 credits,[/FONT]

[FONT=&quot]

You need to include the result you want for question 1 and 2 and also why the result is what is.

[/FONT]

[FONT=&quot]When we know that I'm sure we can provide you some solution.
What does: GPA, FS and failing equivalent grades, mean?[/FONT]

[FONT=&quot][FONT=&quot]A GPA (grade point average) [/FONT][/FONT][FONT=&quot][FONT=&quot]i[/FONT][/FONT][FONT=&quot][FONT=&quot]s a calculated average of the letter grades you earn in school following a 0 to 4.0[/FONT][FONT=&quot]. F is a failing grade. There are also failing equivalent grades WU, FIN, for excessive absences, not taking the final exam, etc., that are also calculated in the average.
[/FONT][/FONT]
[FONT=&quot]
[/FONT][FONT=&quot]For question#1[/FONT][FONT=&quot], I would need the result to be a list of students who have completed the 10 courses, whether they earned a grade or just earned credit, but not to include courses failed. [/FONT]

[FONT=&quot]For question#2[/FONT][FONT=&quot], if the student has all 10 courses, I need a GPA (grade point average) calculation of 8 specified courses out of the 10. GPA calculation is total of (credit*grade point) per course, divided by total credits being calculated. Failing or failing equivalents are always calculated: photo attached for how GPA is calculated.[/FONT]
[FONT=&quot]
[/FONT]

 

[sherikream]

I created a table to emulate what you describe then queried it the best I can based on your criteria.

I eliminated the specific student data as you can do this with a simple join in design mode.

The Courses table looks like this: (Quick and dirty)
StudentID Integer, indexed
CourseName Text
Grade Text
GradePoints Integer
CreditsEarned Integer

SQL to extract the basic information you are requesting.

SELECT Courses.StudentID
     , Count(Courses.CourseName) AS CoursesPassed
     , Sum(Courses.CreditsEarned) AS CreditsEarned
     , Sum(Courses.GradePoints) AS GradePoints
     , Avg([Courses]![GradePoints]) AS GPA, CC.CoursesTaken

FROM Courses INNER JOIN 
[SELECT Count(Courses.CourseName) as CoursesTaken
     , StudentID
FROM Courses
GROUP BY Courses.StudentID]. AS CC ON Courses.StudentID = CC.StudentID

WHERE (((Courses.Grade)<>"F"))

GROUP BY Courses.StudentID, CC.CoursesTaken;

Adjust this to match your table and column names and join your students table to fill in the details you want.

I hope this example helps you get where you're wanting to go for part 1.

Part two:

Write a select query to choose all the students who have passed all 10 classes with the details of those classes included to choose the 8 in another query.
Then write another query limiting the gpa calculation only those 8.
SELECT * from MyQuery Where CourseName IN(Course1, Course2, Course3, Course4 ...etc)

I'll try out your suggestion. But the complicated thing is that the CourseGrade can be CR/P/S which means a student will get credit but have no grade points and these credit only courses will not be calculated GPA calculation. What would a CR/P/S be decoded to in the GradePoints (integer) column since they're not integers?

 

[Insane_ai]

I'll try out your suggestion. But the complicated thing is that the CourseGrade can be CR/P/S which means a student will get credit but have no grade points and these credit only courses will not be calculated GPA calculation. What would a CR/P/S be decoded to in the GradePoints (integer) column since they're not integers?

Use Zero or -1 and filter those out of the selection criteria. I think the -1 value would be good only if it is used as an indicator to exclude the record(s) while allowing a 0 to be used in an average calculation.

It would really help to have copies of your tables and some fictitious sample data to understand the calculations better.


I realize the example below isn't exactly what you need, I am hoping it is close enough to help. More details will make it easer for me to help you.

 

[sherikream]

Use Zero or -1 and filter those out of the selection criteria. I think the -1 value would be good only if it is used as an indicator to exclude the record(s) while allowing a 0 to be used in an average calculation.

It would really help to have copies of your tables and some fictitious sample data to understand the calculations better.


I realize the example below isn't exactly what you need, I am hoping it is close enough to help. More details will make it easer for me to help you.

Yes, definitely, i'm going to post some fictitious sample data very shortly.

 

[sherikream]

Use Zero or -1 and filter those out of the selection criteria. I think the -1 value would be good only if it is used as an indicator to exclude the record(s) while allowing a 0 to be used in an average calculation.

It would really help to have copies of your tables and some fictitious sample data to understand the calculations better.


I realize the example below isn't exactly what you need, I am hoping it is close enough to help. More details will make it easer for me to help you.

Attached is a zip of my sample tables and data.

One more thing I need to add while looking through the data is when a course does not have a grade, it's "in progress" and should not be included in the 10 course count and also not calculated in GPA.

Here's the list of courses the 10 courses that a student needs to pass, the first 8 are the ones that will need a GPA calculated.

ACC 2101
CIS 2200
ECO 1001
ECO 1002
ENG 2100
LAW 1101
MTH 2205 or MTH 2207 or MTH 2610
STA 2000

COM 1010
ENG 2150

 

[Insane_ai]

I've attached a copy of your database example with the queries built to do what you describe (or at least how I interpret your description).


Objective 1: List count of classes for students who have completed 10 courses without failing the course excluding classes in progress. (Students_10)

Objective 2: Calculate the GPA for the students identified in Objective 1 but only calculate for completed classes in the list provided. (Students_8_GPA)


Students_10 finds students who have completed 10 or more courses

Students_10_Courses lists the courses where the students either passed or received credit but are NOT credit -1 or in progress

Students_8_GPA calculates the GPA for the courses listed, note all three MTH Classes are included. If you wanted only one, you would have to write another selection query defining which one to select over the other in case a student has multiples.

 

[Cronk]

The tables in the attached database are not normalized and there will be any amount of trouble getting reports out of it.


The existing table Course should be split into 2
tblCourses
CourseID
CourseNo
CourseName


tblCourseGrades
CourseGradeID
CourseID
Credits
Grade
GradePoint


I'd expect there would be another 2 tables, one for the actual Course Classes, and one for the students in those course classes


tblCourseClasses
CourseClassID
CourseID
CourseYear
Term


tblClassStudents
ClassStudentID
StudentID
Grade


PS Strictly, CourseYear and Term should be in a separate table

 

[The_Doc_Man]

Well, here is my thought. First, I'll go with Cronk that you need to normalize your tables. THAT will go a long way towards getting reports out trivially. So if I my be so bold as to suggest it, if you are not familiar with database normalization, then you need to read up on that subject. To do that, you need to search for one of two topics.

In this forum the SEARCH option is in the thin blue ribbon near the top of the screen just under the block that shows your login identity. SEARCH is the third option from the right on that line. To use that search, click it and search for "Normalization."

In a general web search, search for "Database Normalization" (because there are also topics you could hit for mathematical normalization, political normalization, and perhaps a few others, so just "Normalization" would give you too much to consider.)

Once you understand normalization better, read up on JOIN queries and on SQL Aggregate functions. Because those two features will be prominent in your solution. Also read up on "Table Relationships" (a factor in normalization) because you will have a relationship between courses and students that will require one-to-many or many-to-one relationships. Further, the table that shows what grade a student made in any given course will have to be an example of what is called a "Junction table." The junction table is how you have to implement the list of grades, and is required in that form because you have a "many people can take many courses" case. A junction table is how you do the many-to-many relationship in Access.

If you normalize your tables as Cronk suggested, then you can build JOIN queries that show details of each person with details of each class they took including every time they took a given course (perhaps even once failing and then later succeeding).

You could then use SQL Aggregate (summation) queries to count up courses (COUNT) or add up (SUM) or take the average of (AVG) a particular field. HINT: Build the JOIN query first to show each student's grade for each individual course. Then write your aggregation query to base itself off that JOIN query, because writing a query of a query is PERFECTLY legal and often is also very efficient.

As to knowing whether your students have enough classes, it seems like you don't need to know that. All you need to do is count total credits earned and compare against some target number of credits. Since you get no credits for a failed course but DO get credits for a transferred course, I see no issue. There are a dozen ways to count courses, so that won't be hard for you.

In the query, for example, you can have an IIF that is either 1 or 0 depending on whether the student passes or fails the course (and have a transferred created count as a PASS for that particular calculated field). You could have that as a calculated field in the big JOIN query that I mentioned earlier. Then in the aggregate query, just SUM that column.

On the other hand, if you want to exclude courses "in progress" you will need a way to identify that status, but then that big JOIN query I mentioned could easily have a WHERE clause to not consider such courses.

Since there was a mention of various course statuses other than grades, transfer credits, and special cases like missing the final exam, etc., you could ALSO have a separate table of possible results and have codes in that table to show things like GPA points, course credit points, etc. and use a JOIN with that "course status" table so that you can just sum or count those status fields.

You can certainly write multiple queries to determine individual values if you wanted to do it that way. HINT: If you take THAT approach, write the big JOIN first and then write your narrowly focused queries to base off that big JOIN.

I know this is a lot to chew on, but I tried to give you some ideas that you might find helpful. Do a little bit of reading and save yourself a LOT of headaches later.

 

[sherikream]

I've attached a copy of your database example with the queries built to do what you describe (or at least how I interpret your description).


Objective 1: List count of classes for students who have completed 10 courses without failing the course excluding classes in progress. (Students_10)

Objective 2: Calculate the GPA for the students identified in Objective 1 but only calculate for completed classes in the list provided. (Students_8_GPA)


Students_10 finds students who have completed 10 or more courses

Students_10_Courses lists the courses where the students either passed or received credit but are NOT credit -1 or in progress

Students_8_GPA calculates the GPA for the courses listed, note all three MTH Classes are included. If you wanted only one, you would have to write another selection query defining which one to select over the other in case a student has multiples.

I think we're getting close to what I need!! So excited!!

It seems like the (Student_10) is counting F (failing grade). "WU" is also a failing equivalent grade that shouldn't be counted. So there should be a total of 10 courses with a passing grade or credit earned (-1). If it makes it any difference, CR in the grade column = received credit = -1 in grade point column.

(Students_10_Courses) should to be a list of all the courses of the resulting students in (Student_10), passed and failed, for use of GPA calculation.

In query Student_10, could you explain why >=10 is used twice in the criteria for column "Count"?

 

[Insane_ai]

I'm beginning to wonder if I've done too much already.

Look at the design view and make the changes you need for selection criteria.
Pay attention to the bottom section that builds your WHERE clause and you will have an answer to your question about >=10 on the count column.