By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,490 Members | 862 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,490 IT Pros & Developers. It's quick & easy.

Relationships MS Access

P: 12
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
Aug 10 '12 #1
Share this Question
Share on Google+
16 Replies


Seth Schrock
Expert 2.5K+
P: 2,931
Can you give the field names?
Aug 10 '12 #2

P: 12
Table 1 Field names: Customer ID (PK); Prename; Surname;Address
Table 2 Field Names: ClassID; Date/Time; Exercise; Instructor
Aug 10 '12 #3

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
Aug 10 '12 #4

P: 12
So I need to have 3 tables?
Aug 10 '12 #5

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
Aug 10 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,931
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.
Aug 10 '12 #7

P: 12
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.
Aug 10 '12 #8

Seth Schrock
Expert 2.5K+
P: 2,931
Okay, then you can get rid of the join table (tblStudentClasses) and add a Class field to tblStudents and make a relationship on that.
Aug 10 '12 #9

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.....
Aug 10 '12 #10

P: 12
so in terms of relationship, what fields would i join together
Aug 10 '12 #11

Seth Schrock
Expert 2.5K+
P: 2,931
Assuming no tblStudentClasses...
tblInstructors to tblclasses on InstructorID:Instructor.
tblStudents to tblClasses on Class:ClassID.
Aug 10 '12 #12

twinnyfo
Expert Mod 2.5K+
P: 3,054
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.
Aug 10 '12 #13

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

Thank you Seth and twinnyfo.
Aug 10 '12 #14

P: 12
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
Aug 10 '12 #15

Seth Schrock
Expert 2.5K+
P: 2,931
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.
Aug 10 '12 #16

P: 12
Ok thanks, will do that now
Aug 10 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.