Relationships to multiple copies of a table change after saving

[RobertSmith]

Hello all, first time forum poster. I hope I follow all conventions properly.

I would like to create multiple relationships to a Person table.

Person: PersonID (autonumber PK), LastName (text), FirstName (Text)
Project: ProjectID (autonumber PK), ProjectName (text), ProjectOwner (Number, FK to Person)
System: SystemID (autonumber PK), SystemName (text), TechnicalContact (Number FK to Person), BusinessContact (FK to Person)

Relationships:
Person.PersonID 1->many Project.ProjectOwner


Person_1.PersonID 1->many System.TechnicalContact
Person_2.PersonID 1->many System.BusinessContact

I can set this up in the Relationships window without any problem. However, when I save the Relationships window, close and reopen I see:
Person.PersonID 1->many Project.ProjectOwner
Person.PersonID 1->many System.TechnicalContact
Person_1.PersonID 1->many System.BusinessContact

My ProjectOwner and TechnicalContact will be different, so this 'autorevised" model is not what I need.

Has anyone experienced this type of behavior? Do you have suggestions on how to avoid this issue?

Many thanks for any assistance.

Bob

[June7]

That is standard behavior.

However, suggest you change the Join Type for each link to "Include all records from Project and only those from Person that match."

[RobertSmith]

Hi June7. Thank you very much for replying. I agree the right joins are better and I have updated the relationships as you suggested.

If the relationship remap is standard behavior, how do I model the desired situation where I have three different people: a ProjectOwner, a TechnicalContact and a BusinessContact, if the system is automatically forcing my ProjectOwner and TechnicalContact to be the same person?

[John_G]

The relationships in the diagram are correct. You need two occurrences of Person because the relationships between two tables cannot result in multiple (and different) recordsets at the same time. (I know - that is not explained very well).

For any given person, there are two sets of records defined by relationships between Person and System. One set comes from the relationship using TechnicalContact, and the other from the relationship using BusinessContact.

What Access is doing is separating them on the diagram to avoid the apparent conflict. It is only on the diagram - in reality the relationships don't conflict, and there is nothing that requires the ProjectOwner and TechnicalContact to be the same person.

[June7]

Good explanation John_G.

When you build queries, will have to include the Person table 2 times, for two joins to the two contact fields.

[RobertSmith]

Thank you both. I think I'm starting to get it. It seems the Relationships window is more a mechanism to maintain constraints. It is not necessarily an ER diagram.

When I create a query to retrieve all of my info (adding an Interface table that links Project and System), MS Access will retain all of the table layouts in the query, including all copies of Person.

I was just expecting this same behavior from the Relationships window, which was a bad expectation.