Using subforms and displaying specific data from a table

[dean20]

Hi, I have tables students, assignments, and grades. I am trying to create a form that lists all student records (fields First Name and Last Name) from the students table in a subform while a specific assignment record is in the main form. All students should be displayed and allow for grade entry in this form. The grade table would hold the grades. What should I do with relationships and lookups to make this work?  Thanks in advance. -Dean

 

[DougP]

The key to making subforms and main forms work correctly is how you set up the tables. (what fields are in what table)<br><br>Ok<br>&nbsp;<br>So get a blank sheet of paper a sketch how you want the form with its subform attached to look<br>Then write in which field you want to see in both.<br><br>OK<br><br>Now the main table or form and the subform need a Link to tie them together. <br>Such as StudentID in your case and ClassID<br>OK<br>&quot;Usually&quot; when you have 2 different items that are related you need 3 tables<br>1 holds the Students<br>1 holds the Class name<br>1 holds Which Student is in which class<br>So table 3 can have one record for each student for each class<br>Example: Table3<br>StudentID ClassID<br>1234 Math<br>1234 Science<br>1234 English<br>3456 Science<br>3456 History<br><br>So student 1234 is in 3 classes as shown<br>and student 3456 is in 2 classes one of which is the same as 1234<br><br><br>

 

[elizabeth]

Following DougP's example, I'm assuming you will eventually have multiple classes.<br><br>tblStudent: StudentID, FirstName, LastName, other desc data about student.<br>tblClass: ClassID, ClassName, other descriptive data about the class.<br>tblAssignment: AssignmentID, ClassID, AssignmentDesc, DueDate, etc.<br>tblGrade: AssignmentID, StudentID, Grade. <br><br>relationships: 1:n means 1 to many. Turn referential integrity ON.<br>tblStudent 1:n tblGrade<br>tblClass 1:n tblGrade<br>tblAssignment 1:n tblGrade<br><br>Base your main form on a query based on tblAssignment and tblClass, sorted by tblClass.ClassName and then by tblAsignment.DueDate or whatever you prefer. Base your subform on a query on tblGrade and tblStudent sorted by tblStudent.LastName or whatever you prefer. After creating both forms, use the wizard to place the subform on the main form. With these relationships the wizard should link your forms properly.