Realtionships

[dsaxena15]

Hey guys!

Is it possible for the primary key of one table to act as the foreign key in another table for more than one columns?



What I'm trying to do is create a table for a Committee which will have 1 student and 5 professors! So Can I import the faculty ID for each of the 5 faculty members?

When I try creating the second relationship, access automatically creates a new Faculty table for the relationship! I have attached an image for you guys to see!

Thanks a lot!

[June7]

Yes. This will require multiple joins to the FacultyTable, just as you are doing. The multiple instances of table is correct.

[E. Rumsey]

I think (please correct me if I'm wrong, other users) that your other option is to create a many-to-many relationship between the Faculty Table and the DQE Committee Table. In that cause you would need to create a join table (http://office.microsoft.com/en-us/ac...010096320.aspx) between the two, but you would not need five copies of the Faculty Table as you have/will have in your example.

[June7]

Yes, the alternative is to normalize the members into a related child table. This would mean a record for each member for each committee.

It is a balancing act between normalization and ease of data entry/output.

The original structure can complicate some search operations, such as:

Find all the committees that faculty Joe Smith serves on.

This requires the same criteria under each member field but with OR operators.

[dsaxena15]

Yes. This will require multiple joins to the FacultyTable, just as you are doing. The multiple instances of table is correct.

Okay, so when the data is being entered, would it have to be entered separately in all the instances of the Faculty table or would it show up in all the instances by just entering it in the main Faculty table? Thanks a lot! I really appreciate your help!

[dsaxena15]

Yes. This will require multiple joins to the FacultyTable, just as you are doing. The multiple instances of table is correct.

Also, what do these multiple instances signify? I mean why do they even show up?

[June7]

There is only one Faculty table, enter data to the table and it will pass through to the multiple instances in query. Each field that has FacultyID as fk must join to its own copy of the Faculty table in order to retrieve related info.