Multiple tables on a form

[Roxetta]

SITUATION: I'm a teacher creating a database for my Math classes. I have 5 different classes (Math1-Math5). Each class is represented by a table that contains the Students Name and Section Number. Each class has different assignments (Assign1-Assign10). Each assignment has a Grade.

PROBLEM: I'm building a form that would allow me to update the grades for each assignment based on the class. Here it is, the form is based on the section number. Immediately below is a subform based on a query that crosses the students with the assignments. It is linked to the main form on the section number. I need to update grades for each student/assignment. How do I update the grade table using the AssignmentID and Student ID?

 

[CEH]

First, you need to fix your schema.... You do not need a table for each class. Also a separate table for containing Student name. Example....for tblClass... PK ClassID, ClassDescription....
for tblStudent...PK StudentID, StudentName....
You would also have a table for Assignments, and another for Grade.
(I'm not sure what you mean by "Section Number" but it might need a separate table also....... After you do this and get the relationships set up, you might find it easier to work with.

 

[Roxetta]

Thanx for your quick reply, CEH. Let me clarify so that you can understand better. I have the following tables: tblClass (contains ClassID, ClassName, Section#, Location, Period, etc.), tblStudents (contains StudentID, Student Name, Section#), tblAssignments (contains AssignmentID, Section#, Assignment Description, Max Points), tblResults (contains AssignmentID, StudentID, Grade). The whole purpose for the form is to be able to add results to tblResults while seeing related info from tblClass and qryStudentsXAssignments.

If it helps, I want it loosely based on the following microsoft template - http://office.microsoft.com/en-us/templates/TC010184071033.aspx?CategoryID=CT101426031033.

Does this better explain?