This is actually a good case of database normalization. Read here for a basic rundown, which actually uses a student-class example.
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q100139
I would recommend a three-table structure.
tableStudents
StudentID, Autonumber or properly formatted Long Integer Number/Text field, set unique. (PK)
other student-specific fields, name, address, etc...
tableCourses
CourseID, Autonumber (PK)
CourseNumber, Text or properly formatted Long Integer Number/Text field. Probably not unique since courses repeat semester after semester.
other course-specific fields, department, number, room, etc...
tableStudentsCourses
Two fields, use the lookup wizard to pull in
StudentID from
tableStudents and
CourseID from
tableCourses. Include at least the
*Name columns and include, but hide, the
*ID columns. I would include the
Department, Course Number, and Course Name in the lookup box for reasons that will become clear later.
Highlight both fields, hold shift and make them a multi-part primary key. You should end up with two 'key' symbols, and they will say they are not unique. Independently, they're not. But every combination of a particular student taking a particular course offering, is.
Now go into Tools>Relationships, show your three tables, and click on the join lines between those related fields. Both should be One-to-Many, with Enforce Referential Integrity turned on. Cascade Update is helpful if you're not using autonumbers.
Now for your forms. You can design them in either order, but I usually design my subforms first and then my main forms, so I know how much space it will take up.
Your subform will be based on
tableStudentCourses and have only one bound field, tied to the
CourseID field (which itself is a lookup to your
tableCourses). Then add two unbound text boxes and make the row source for them
=CourseID.Column(2) and
=CourseID.Column(3), respectively. You can lock/disable these fields to avoid error messages, but the fields are not updateable; they're just references. I also make this sort of subform 'Continuous' instead of 'Single'.
Your main form will be based on your
tableStudents (or a relevant query of the same) and format it as you wish. Leave room somewhere for the subform containing the course information. Add it with the button on the Toolbox for Subforms/Subreports and select your previously made subform.
That's about it, unless I left something out you don't know how to do. There was no coding involved, and now you can add courses (with a separate form), print reports of who is in what courses and vice versa, etc. I know it looks like a lot, but I tried to go into a lot of the theory behind it so you know what you're doing. In case you didn't realize it, you just made a many:many relationship between your students and courses. There are of course other details, like drawing your department list from its own lookup table, etc, but that's not part of this question.
Good luck,
David R
[This message has been edited by David R (edited 02-28-2002).]
