Students as instructors

[Maarkr]

I have a typical tblStudents, tblJct, tblClasses set up as many-many.
A field in the students table is a yes/no field to identify the student as
an instructor.
How would YOU setup the tbl/relationship so you can assign/identify many
instructors (who are in the students tbl) for each class?

 

[Jeff Boyce]

The key word in your description may be "many". If you have the possibility
of more than one, you might need another table.

In fact, if one student can be in many classes, and one class can have many
instructors (and a student can be an instructor), consider the following
table design:

tblPerson
PersonID
FirstName
LastName

tblClass
ClassID
ClassTitle

tblRole
RoleID
Role (e.g., Instructor, Student, ...?)

trelParticipant
ParticipantID
ClassID
PersonID
RoleID

Of course, if your class was offered more than once, you'd also need to add
a table for instances of the class ... you probably wouldn't have the same
students (or maybe even the same instructors) for all instances of the same
class.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Maarkr]

thanks... I do have many classes:
tblJct
JctID
PersonID
ClassID
Attended

I was trying to setup the Classes table with an InstructorID linking to an
instructor table, but was not sure the best way to keep the instructor table
updated with the current instructor list (maybe running a make table query
every time the db opens)?
or is that just not kosher?

 

[Jeff Boyce]

If one class (instance) can have many instructors, that's a one-to-many
relationship. The table trelParticipant that I offered gives you a way to
record all students AND all instructors for a given class. The tblJct you
described doesn't indicate whether the PersonID is a student or an
instructor (but I'm guessing you are referring to students, since you have
an [Attended] field.

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP