Forum Discussion

ns61's avatar
ns61
Copper Contributor
Mar 14, 2025

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_gp's avatar
    arnel_gp
    Steel 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)

Resources