Relationships MS Access

[and111]

I am creating a database and unsure how to continue.

I have two tables created so far: Members, Instructors

Each student can attend only one class; but each instructor can teach multiple classes. I have linked Customer ID as Primary Key with Course ID. However they are linked in the order they are shown. I want to link multiple customers with a Class.


I hope I have made sense.



Thanks in advance!!

Andrew

[Seth Schrock]

Can you give the field names?

[and111]

Table 1 Field names: Customer ID (PK); Prename; Surname;Address
Table 2 Field Names: ClassID; Date/Time; Exercise; Instructor

[twinnyfo]

You should have some sort of Enrollment table that has foreign keys for students, instructors and courses. This should allow any combination for your classes.

[and111]

So I need to have 3 tables?

[twinnyfo]

I would recommend at least these:

1 Table listing all your students
1 Table listing all your courses
1 Table listing all your instructors

1 Table listing all enrollments, which would use the PK from the other tables to know which students are taking which courses (which gives the instructor, too).

Depending on if you have different terms or semesters, you would have a table for that as well.

Structure is simple, but would allow you to adjust instructors to classes as necessary.

Please let m eknow if you have any additional questoins.

[Seth Schrock]

I believe that you will need 4 tables. Since each class can have many students and each student can take multiple classes, this forms a many-to-many relationship which requires a join table. So your tables would be like the following:

tblInstructors
InstructorID PK
Prename
Surname
Address
etc.

tblStudents
StudentID PK
StuPrename
StuSurname
StuAddress
etc.

tblClasses
ClassID PK
ClassName
Instructor
NumberOfCredits
etc.

Your join table would be
tblStudentClasses
StudentID PK
ClassID PK

Instructors would be related to the classes, classes to the join table and students to the join table.

[and111]

Ok thanks for your answers, but the database I am making means that one student can only join one class but a class can have multiple students.

[Seth Schrock]

Okay, then you can get rid of the join table (tblStudentClasses) and add a Class field to tblStudents and make a relationship on that.

[twinnyfo]

Of course, this assumes that a student would only ever take one class. If the student returns for another class, you still need the tblStudentClasses so keep a record of which classes the student took in the past.....

[and111]

so in terms of relationship, what fields would i join together

[Seth Schrock]

Assuming no tblStudentClasses...
tblInstructors to tblclasses on InstructorID:Instructor.
tblStudents to tblClasses on Class:ClassID.

[twinnyfo]

Seth gave a pretty good example in #7 above. That should really be your starting point. There will probably be more info you need to build into this table, but this should be the skeleton you start with.

[and111]

Ok, I managed to do it on MS Access and its working.

Thank you Seth and twinnyfo.

[and111]

Sorry for asking another question, but do you know how I would make a query which would list the classes a particular instructor must attend

[Seth Schrock]

As this is a separate question from the original topic, please start a new thread and I/we can help you there. I'm not an admin for this website, or I would move it for you.

[and111]

Ok thanks, will do that now