Self Joins and Many-to-Many Relationships

[B Kava]

Relatively new Access user and first-time poster. My question pertains to self-joins and many-to-many relationships. The ‘rough’ database I created thus far involves relationships among individuals, sort of a genealogy concept. I will eventually grow this to try and show many-to-many relationships between and among individuals, families, corporations, and government agencies. The attached database has four tables (tbl_Individuals, tbl_Relationships, tbl_RelationshipTypes, tbl_RelationshipRoles), a query (selQry_Relationships), a form (frm_Individuals), and a subform (subf_Relationships). From the research I’ve done on this topic, I think I have correctly set-up the tables and relationships, though I wonder if I don’t need both a RelationshipRoles table and RelationshipTypes table. Seems they could somehow be combined. I think I have the query set-up correctly, too, but I don’t know where it comes into play. The plan is to use the main form to show/add new individuals and the subform to show/add new relationships. So far, I am able to add new individuals and create new relationships. What I can’t figure out is how to show the opposite relationship (bi-directional) in the Relationships subform. For example, the first individual in my table and form is Bob Smith. He is the Father of Tom Smith and the Grandfather of Joe Smith. When I move to the next record in the form, Tom Smith, the relationships subform shows that he is the Father of Joe Smith. It does not show that he is the son of Bob Smith. And when I move to the third record on the form, Joe Smith, no relationships are shown for him in the subform. I would like it to show that he is the Grandson of Bob Smith and the son of Tom Smith. Thoughts? Any help is greatly appreciated. Thank you!

 

[CJ_London]

I think you need to rethink your design a little bit. You will also need to use VBA because you have a recursive relationship which can only be handled in VBA.

On the design I would add a gender column to your individuals table - then you don't need the relationship types or roles tables table as such since a female offspring of a male can be calculated as father/daughter. two males with the same father would be brothers, etc. Not clear whether you are mapping bloodlines or relationships - if the latter you may need something to indicate 'stepfather' or 'stepbrother' for example.

You may also want to consider including dates in your table to indicate dates of birth/marriage/death etc.

see this link about how to create a recursive function

https://stackoverflow.com/questions/763016/is-it-possible-to-create-a-recursive-query-in-access

this uses the concept of levels and nodes. By navigating from one individual to another if up one level then that individual is the father or mother depending on gender and if across one node they may be aunt or uncle. and if up two levels they are grandfather/mother. etc

and if going the other way, down one level will be son or daughter, two levels will be grandson/daughter.

You have chosen a complex subject as a starter project. I'm sure there are others who will provide similar suggestions. You may also want to take a look a the code repository and sample databases part of this forum, there may be examples there to help you. The subject is a common one so also use the search tool for threads on the same subject

 

[B Kava]

Thank you CJ and Gizmo. I will research the info you provided. Tis a project I can only work on weekends, and then very little at that. Slow developing, you might say.