Join Query for Student Grades Database

[usmcgrunt]

BLUF: How can I have a query that displays all students in one column, all exams in the next column, and a blank column, initially, to enter grades? So the student name would repeat in the first column for each unique exam in the second column. In my case, we have 242 students and 32 exams. That means that there would be 7744 rows in this query That could be filtered by student, classroom, and exam.

I am in the early stages of redesigning a grades database for my institution. I have had alot of help from countless forum members on setting up the initial design and I am feeling good about where it is going.

Well, I have run into a problem right out of the gate. I would like to have a form, preferably splitform, that lists all the students in a classroom along with all the exams they will take. This will allow the instructor to quickly filter the form by exam and then enter in all of the students grades in the bottom datasheet.

I am just beginning to understand relational database normalization and that is what prompted this database redesign. Previously, all the student info along with all associated grades were in the same table. Picture a table with 242 rows (students) and 39 columns of which 32 were the exams.

Background:

***Table Design***

tblStudents
studID (Primary Key)
studName
studClassroom

tblGradedEvents
geID (Primary Key)
geName

tblGrades
studID
eventID
eventGrade

Relationships:

tblStudents.studID has a one-to-many relationship with tblGrades.studID
tblGradedEvents.geID has a one-to-many relationship with tblGrades.eventID

***Query Design***

qryGEventEntry
tblStudents.studName
tblStudents.studClassroom
tblGradedEvents.geName
tblGrades.eventGrade

SQL:

SELECT tblStudents.studName, tblStudents.studClassroom, tblGradedEvents.geName, tblGrades.eventGrade
FROM tblGradedEvents
INNER JOIN (tblStudents INNER JOIN tblGrades ON tblStudents.[studID] = tblGrades.[studID])
ON tblGradedEvents.[geID] = tblGrades.[eventID];

Now, this code does exactly what it is supposed to do and that is list all students and only those students that have a grade(s) on an exam(s).


***What I am trying to accomplish***

Based on my research, I think it is an outer join but I cant get it to work likely because I have three tables in the query instead of 2. What I'm looking for is a query that will list all students along with an associated exam even if a grade has not been assigned yet. By way of example, lets say there are 15 exams in the tblGradedEvents and 15 students in the school. the query would have a total of 225 rows; 15 rows for each student even if a student has not received a single grade yet.



Thanks in advance,

Sean

[kennejd]

I'm a little hazy on your design...how do the associated exams get assigned to the student? It seems like there would be a student table...a class table....then a class_student table. Then an exam table then a class_exam table. Then a exam_student_grade table for holding the grades. You probably have it set up ok and just haven't shown us.

Anyway, for your outer join, the syntax would look like the following....if your goal is to list all students...even if they don't have a grade:

SELECT ....
FROM tblStudents s
LEFT OUTER JOIN tblGrades g
on s.studid = ge.studid
LEFT OUTER JOIN tblGradedEvents ge
on g.eventid = ge.geid

[usmcgrunt]

kennejd, Thank you for responding to my post. I was beginning to think no one was going to offer up some matches to help me light my fire.

You probably have it set up ok and just haven't shown us.

You give me way to much credit. I have been searching the net for over a week trying to figure out how to design this database. Nothing exactly matched what I am trying to do so I just started piecing together bits of code from here and there and building tables based on expert advice from people like Allen Browne. That brings me to my current situation which is the question you ask below.

how do the associated exams get assigned to the student?

I thought I could just create a students table, grades table, and graded events table and relate the students table to the grades table by student ID and the graded events table to the grades table by graded event ID.

Then I created a query with the following fields:

student ID from student table
student name from student table
student classroom from student table
graded event name from graded event table

Now when I run this query I get nothing. Keep in mind, there are no grades in the database at this time.

What I would like to have happen is that every student will be listed 32 times, once for every grade he/she will take.

It seems like there would be a student table...a class table....then a class_student table. Then an exam table then a class_exam table. Then a exam_student_grade table for holding the grades.

If you could explain your rational for all of these tables, that might get me going in the right direction.

I have attached a picture of the current relationships in the database and the form that I am trying to use to enter the grades.

Background on the form. The instructor can filter the form by classroom, student, and graded event and then enter grades for the students right in this form.

Thanks,

Sean