I don't even know my problem with these relationships.

[ClearlyAnIdiot]

I can't even begin to comprehend where to start with this.
There are 6 tables involved, where 4 of them are practically the same. There's the companies, people, and 4 different roles for the people. I've keyed the peoples' names and the company numbers, as well as names in the roles. Apart from names and company numbers, I want a company to show its directors, etc., and people to show what they're the shareholders of, etc.. A company may have multiple people fitting into the same role, and a person may hhold various positions over multiple companies, including up to 2 positions in the same company. How can I set up the relationships so that when I update a person's record, it will automatically update all 5 other tables, and the same for companies? So far, I have a one-to-many from the company number to roles, from the person's name to roles, and from the roles to the roles of the company.

PS: The role tables are basically the same as the people's table, but with only 2 fields. If the solution means that I have to get rid of those 4 tables, I don't really mind.

 

[Cronk]

If I understand your situation, you need 3 tables. One for people, one for companies and one, a junction table where people are connected to companies.

tblPeople
PK PeopleID
Other person fields

tblCompanies
PK CompanyID
CompanyName
Other company fields

tblCompanyPeople
PK CompanyPersonID
FK CompanyID
FK PeopleID
PersonRole

This provides for a person to be associated with many companies and having various roles in different companies.

Also multiple people can be associated with any company.

This is not easy to get your head around but once it clicks, it's so obvious.

 

[jdraw]

Here is a great tutorial that may help with understanding tables an relationships.
Good luck.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[ClearlyAnIdiot]

It "clicked" to me in the middle of doing it. Thanks!