The answer is in the name of your question. You need some join tables to support the many to many relationships involved.
Note that "classes" can have two different interpretations depending on how your school is organized. You might have a "class" of Mathematics with "sessions" or "periods" to which students are assigned to. You might have first period students taking math and a different group of students taking math during second period. Or you might choose to consider each Time slot that you teach a "class" with a specified subject for that time slot. It will help us to help you if you clarify those distinctions.
But you will definitely need Join table facilitating a link between students and assignments:
Students::__pkStudentID = Student_Assignment::_fkStudentID
Assignments::__pkAssignmentID = Student_Assignment::_fkAssignmentID
The grade or score that a student gets for completing an assignment can be recorded in a field that is defined in Student_Assignment.
Your portal to list assignments on a student layout would not be a portal to Assignments but rather a portal to Student_Assignment. Descriptive fields from the assignment table may be included in this portal.
You would also link assignments to either a "class" or a "session" via a different relationship linking assignments to classes. Once you have made that link, a script can populate the student_Assignment table with a new record linked to that new assignment for each student attending that class.
Thanks for the quick response. Unfortunately, I am being unbelievably dense...
So I've tried linking Assignment to Classes and Assignments to ClassAssignments and then Class. It says I have to create a new table to do that. I know this is something simple but I just can;t wrap my head around it.
Thanks again for the help though.
Better re-read that dialog that pops up. You are not being asked to create a new table, but rather a new occurrence of one of your existing tables. This adds another "box" to the relationship graph, but does not add a table to your database as the boxes, table occurrences as we call them, are not actually tables, but the means to access a table via a specific name and set of relationships.
You may find this tutorial useful:
Got the table occurrence created. But now I'm trying to figure out how to create the portal that will allow me to create the necessary related records. I know I have to run a script when a student is added to a class. Does this sound right?
(I know this isn't in the script form yet, just trying to figure this out)
Trigger Script when adding Student
Get __pkClassID, __pkStudentID
Count # of Assignments associated with Class (12 for example)
Go to Layout for StudentAssignments
Set Up Loop to repeat Count # Times
Get __pkAssignmentID for Assignment Record #
Create New Record putting StudentID and ClassiD into _fkStudentID and _fkClassID and __pkAssignmentID for Assignment Record # into _fkAssignmentID
End If # = 0
Return to Original Layout
Does this look right?
Your script seems based on the assumption that a newly added new student should be assigned all previous assignments.
The main idea is correct but not all the details. You would use the relationship between class and assignments or ExecuteSQL to get a list of assignmen IDs in a variable. Your loop can then get the needed IDs from the variable in order to create the needed records in the join table.