Forum Discussion
ns61
Mar 14, 2025Copper Contributor
Reciprocate Relationship - how to setup
I think I almost have this...and then I don't.
I'm creating a db that has a single 'Contact' table (ContactTbl - think 'Customer' table or 'Entity' table...). The table has both Organizations and Individuals - I do have a 'ContactType' field (Organization/Individual). I've created a form (ContactF) that brings in the OrgName, FName, LName....(the OrgName or F/LName fields are populated pending ContactType) - that's not the problem.
I have a SubForm (RelationF) that...when an Org record is displayed on the Main/Parent form (ContactF), the SubForm (RelationF) shows all the Ind (employees) and their titles (titles in a free-form txt field for now). When an Ind record is displayed on the Main/Parent form (ContactF), the SubForm (RelationF) shows the Orgs that the Ind works for (has relationships with) and their title with that org (an Ind could work for multiple Orgs).
I actually have this working but all the input in manual...in other words, if on XYZ Corp record and I add an Ind as a relationship, I have to go to the Ind record and create the relationship - that's the issue that I'm having...how can I have Access create the relationship automatically. In other words, when I'm creating the relationship from the Org record, I want Access to create the relationship on the Ind record (since I'm making that relationship through the SubForm. Same thing the other way around - if on the Ind Record and a relationship is created with an Org record, then have Access automatically create the relationship on the Org record.
That way when viewing the records through the form, I can see the proper relationships:
How can I have Access automatically create the relationship.
- arnel_gpSteel Contributor
cross posting on:
Reciprocate Relationships...automatically - how to setup
1 table is not sufficient for your situation.
1 person can have many companies
1 company can have many persons.
you need Company table, Person table, and the Junction table.
Company table:
OrgID (long, auto)
OrgName (short text)
Person table:
PersonID (long, auto)
FName (short text)
LName (short text)
you will need to save the "many" side to the junction table.
junction table will include:
OrgID (long)
PersonID (long)