Linking Tables (1 Viewer)

[sdollen]

Okay...I think I'm getting a better understanding on how to build tables and relationships (database ones ) from reading these forums. So, as an example, I've learned that if I wanted to build a database of students with their info such as social security, address, classes they've taken along with the teachers they've had, I could build three tables -

1. Student info with a StudentID as the pk
2. Class info with a pk
3. Teacher info with a pk

then I would link the student info table and the class info table together by placing a field in the class info table such as "StudentNumber" and relating the pk from the student info table to the "StudentNumber" field in the classinfo table.

Right?

Hope that wasn't too confusing...

If I'm on the right track, it raises a question.

If I'm going to relate the student to the class with the linking field, I'm going to have to manually enter the number that is in the StudentID field on the Student info table into the "studentnumber" field in the class info table.

Right?

So..... what if I have a thousand students and a thousand classes? I have to know what a students ID is in order to enter it into the class info table to link the student to a particular class they have taken. Isn't that time consuming and error prone?

I must be missing something?????

Please someone shed some light on this total beginner.

Thank ya I hope my question and example wasn't too confusing. But, if you are tired and need something to put you to sleep, come read the post often

Thanks again!

 

[DBL]

You might need another table. You have a many to many relationship between classes and students. Each student can take many classes and each class can have many students but each class and student is unique. In the additional table you would need the StudentID field and the ClassID field. Link these to the class and student tables by the appropriate ID fields. You would then enter all your student data into the student table and all the class data into the class table.

You could then do it two ways to link the two together. You could have the student as the One side of the relationship and the class as the Many. Create a form that has the student information as the main part of the form and a subform that would list all the classes that student has taken. The subform would be made up of the join table and would be linked to the main form by the Student ID field.

In the subform delete the Class ID field and use the combo box wizard to a combo box that selects the Class ID and class from the class table. The combo box should be bound to the Class ID field (you can hide that column in the combo box and just view the class name). Then, when you select the appropriate class(s) for the student, the class id number would be automatically saved to the join table.

Or (!) you could do it the other way, have the class as the one side on a form and the student as the many subform. You could then use a combo box to select the ID numbers (or names) of the students taking each class.

Using combos is the best way to avoid user error although it's not fool proof!

HTH