Results 1 to 7 of 7
  1. #1
    whynotman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3

    Linking 2 persons from my "People" table on a subform, a Parent-Child problem?

    Hello all,




    I apologize for the long text.

    I wanted to create a database for work. Being completely new to microsoft office, in the last 2 months I have already spend hundreds of hours working on this, watching youtube videos. I even paid for 599cd.com tutorials which are great but I am poor.

    I am almost there, but now am at a final hurdle.


    I am trying to create a database that among other things includes people, organisations, events, .. which can be linked to each other.
    Each of these components has their own mainform with several subforms attached in tabpages..

    So, for instance, a person would have a mainform with his identity and then tabpages consisting of subforms. i.e.
    -A subform "events", that shows what events he went to.
    -A subform "organisations" which shows the organisations he belongs to.
    And so on
    But also the other way around, when I click on the "Events" mainform it has a: "people" subform which shows the people that attended, an "organisations" subform etc..


    Of course when I add a person to an event, I want this to automatically add this event to the "events" subform on this person's mainform.


    Where I have succeeded so far:
    For linking different tables such as Events and Persons, i just took the easy road and created a junctiontable, a many to many relationship and a form based on the junctiontable. No query.

    i.e.
    I have a table Persons, a table events and a table organisations.

    I have a Junctiontable Person_Event that has 4 fields:
    1. Id (auto)
    2. PersonID (number)
    3. EventID (number)
    4. Comment (short text)
    + many to many relationships: PersonTable -- Junctiontable -- EventTable.
    I created 2 forms linked to this junctiontable:
    A subform Person_Event;
    A subform Event_person.
    I basically just switched the taborder for PersonId and EventID on these different subforms. I then dragged these subforms into the respective tabpages on the mainform for Person and for Events. And lo and behold, it works exactly as I wanted: Acces automatically chooses the right Parent ([ID])and Child link([PersonID]).

    -------

    What doesn't work:
    I now run into a problem that my primitive Acces brain can't handle:
    On a person's mainform, I also badly need a subform "people" to link persons to each other.
    Where before I got my ID's from 2 different tables (i.e. people and events), I now want to link 2 ID´s from the same table (people).

    So far I used the same method as above:
    I made a junctiontable People-People with 4 fields:
    1. Id (auto)
    2. Person1ID (number)
    3. Person2ID (number)
    4. Comment (short text)
    made the relationship and then created a subform based on this table.


    I will alway run into the same problem:
    Example: I go to the mainform of Al Smith, open the subform "people" and I put Al Smith as Person1ID and Betty Doe as Person2ID.
    As long as I am in Al Smith´s form I can see this relationship, there is however no trace of this relationship in Betty´s form. Although the relationship shows correctly in the junctiontable.

    I figure this is because the parent-child link acces automatically makes made it so it only shows the relationships in which the person whose mainform I am viewing is person1ID. I know this because if I remove the parent-child link completely it will just show all the records in the junctiontable.


    So a long text just to ask a simple question.
    How can I make the subform show me all relationships in which Al Smith is person1ID and/or person2ID?
    Is there a way to make this happen by just adding a certain Parent and Child link? Or am I going at this the wrong way and do I need to rebuild this subform a completely different way?


    Thank you for your time, I very much appreciate any and all help.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by whynotman View Post
    How can I make the subform show me all relationships in which Al Smith is person1ID and/or person2ID?
    ???
    Do you have this Al Smith registered twice, or what? Or have you 2 different persons with same name? And what want you achieve with this?

    In case you want to group people, you have to define those groups (e.g. families, or departments, or classes, etc.) in some table- like tblGroups: GroupID, GroupName.
    When any person can belong only to single group, and this group never changes or you don't need to keep the history of it, you can simply have GroupID as foreign key in persons table.
    When not previous, you need junction table, like tblGroupPersons: GroupPersonID, GroupID, PersonID, [ValidAt] (ValidAt is optional - it's needed when you want to keep the history).

  3. #3
    whynotman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3
    Quote Originally Posted by ArviLaanemets View Post
    ???
    Do you have this Al Smith registered twice, or what? Or have you 2 different persons with same name? And what want you achieve with this?
    No, there is one Al Smith.
    I hope i can make this clearer.

    A little update to my junctiontable People_People

    junctiontable People-People with 5 fields:
    1. Id (autoID)
    2. Person1ID (number)
    3. Person2ID (number)
    4. ConnectiontypeID (number)
    5. Comment (short text)

    ConnectiontypeID has a one to many relationship to the table "Connectiontype". In here are a bunch of possibilities such as "partner" "ex partner", brother, sister, ...

    Without the use of a query, I made a form of the junctiontable and changed the fields to comboboxes to easier select people and connectiontype.
    This form was placed as a subform in the mainform of a person. Acces automatically made the parentlink [ID] and the childlink [Person1ID]

    So while now I might have the following in my junctiontable:
    ID / Person1ID / Person2ID / ConnectiontypeID / Comment
    1 / Al Smith / Betty Doe / Partner /Since november 2019


    In the future they might no longer be partners. But I would still like to know they were at one point.
    So I want to be able to change the above record with ID1 to the following: (and not just create a new record or delete an older one)

    ID / Person1ID / Person2ID / ConnectiontypeID / Comment
    1 /Al Smith /Betty Doe / Ex-partner / were partners nov 2019 untill november 2020

    Now everything above works perfectly in my subform. Since this subform is directly connected to the junctiontable I can add and edit every record.
    But the problem is that I can only see this above record when I am in Al Smith's (who is Person1ID) mainform looking at the subform.
    As Betty is Person2ID in this connection, the subform in her mainform won't show this because as said, Acces made the parentlink [ID] and the childlink[Person1ID].
    I would have to duplicate this connection with Betty as Person1ID. By hand this would be tedious and prone to being forgotten by the user.

    I see a few options, but none which I know how to pull off (don't forget I am a complete noob):
    1. I let Acces automatically duplicate this record and at the same time do a switch between Person1ID and Person2ID. But that just seems pointless dataconsuming.
    2. Some way to tell Acces that when I open this subform I don't only want to see the records where this specific person is Person1ID but also where he is Person2ID. So some master/parent/child link magic.
    3. I use no masterlinks and let the subform show me all records, and I filter some other way on the specific person's ID in both fields. preferably automatically so the user doesn't need to filter first.

    I am obviously really hoping option 2 is possible..

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Some comments/concepts for consideration.

    Sometimes the same table can be used to source other concepts/roles.
    eg People --Victim, Perpetrator, Investigator, Lawyer, Judge---depends on the circumstances and context.

    This concept may also be called Reference Tables, or a role/rolename in modelling.
    eg. ISOCountryCodes

    each of these could get their appropriate values from the ISOCountry table.
    SupplierCountry
    SellerCountry
    ShipperCountry


    Always a good idea to create a data model of the proposed "business". Then, test your model with some sample data and sample scenarios. See "stump the model". Make sure your tables support the scenarios you are dealing with. I would not rely on forms to handle relationships.

    I also watch the 599CD videos by Richard Rost. Recently (Aug 2020) he put out a few videos on Genealogy that may be useful. There are many articles in this Database Planning and Design link.
    Good luck with your project.

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by whynotman View Post
    2. Some way to tell Acces that when I open this subform I don't only want to see the records where this specific person is Person1ID but also where he is Person2ID. So some master/parent/child link magic.
    At first, you need a UNION query to get swapped the fields of the junction table, so, in the united fields you get all the instances of each person, in first case (field) as primary person in the relationship and in the second as secondary (Friend). Use a third field (IsPrim), to indicate the direction of the relationship.

    Use the query bellow to create a detailed query for the subform and link the subform with the main form on field "Person". A symbol or a special format based on the field [IsPrim] could inform the user who is friend whom.

    Code:
    SELECT 
    p.PerID1 AS Person, p.PerID2 AS Friend, -1 AS IsPrim 
    FROM 
    tblP2P AS p 
    UNION 
    SELECT 
    f.PerID2  AS Person, f.PerID1  AS Friend,  0 AS IsPrim 
    FROM 
    tblP2P AS f
    tblP2P is the junction table.

    If the relationships have a hierarchical format, it needs a different approach without a junction table.

    The "Connection type", I think that falls in the "calculated field" case. For example, if two persons belongs in the same organization, then are partners, and, if have the same father/mother, are brothers, and probably could be connected with more of one connection.

    Cheers,
    John

  6. #6
    whynotman is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    3
    I added a UNION query, but it doesn't solve my problem.

    The Union query works. When run, it does indeed duplicate the row and switches Person1 and Person2.
    However, it is impossible to add new records.
    Also, when I try to add the subform to my mainform with parentlink ID and childlink Person1ID I get the error
    "the specific field Person1ID could refer to more than one table listed in FROM clause of your SQL statement".

    Anyway, I lack the skills to solve it the way I want, maybe I should go another way.

    Perhaps
    3. I use no masterlinks and let the subform show me all records, and I filter some other way on the specific person's ID in both fields. preferably automatically so the user doesn't need to filter first.
    ?
    Would it be possible to have a filter or search on the subform that shows all results of a specific ID on both the Person1ID and Person2ID column? Maybe have it ON LOAD so it happens automatically when the subform is viewed?

  7. #7
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by whynotman View Post
    However, it is impossible to add new records.
    You don't have to use the recordsource of the form for your actions into the underlying tables. Forms can shows usfull info about the underlying tables and, at the same time, you can act (add, edit, remove etc) directly to the related tables.

    Quote Originally Posted by whynotman View Post
    Also, when I try to add the subform to my mainform with parentlink ID and childlink Person1ID I get the error
    "the specific field Person1ID could refer to more than one table listed in FROM clause of your SQL statement".
    Try to add the name (alias) of the tables in the field references of the field Person1ID, as seems in my previous post.

    Quote Originally Posted by whynotman View Post
    Anyway, I lack the skills to solve it the way I want, maybe I should go another way.
    Take a look in attachment to see how my suggestion works and then make your decisions.

    Good luck with your project!

    Cheers,
    John
    Attached Files Attached Files

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 1
    Last Post: 02-12-2019, 03:11 AM
  3. Replies: 5
    Last Post: 01-04-2017, 02:13 AM
  4. Replies: 1
    Last Post: 10-04-2016, 04:29 PM
  5. Problem using Parent/Child Form/Subform
    By EvanRosenlieb in forum Forms
    Replies: 4
    Last Post: 06-27-2011, 05:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums