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!
Self Joins and Many-to-Many Relationships (1 Viewer)
- Thread starter B Kava
- Start date
- Local time
- Today, 23:23
- Joined
- Jul 9, 2003
- Messages
- 16,306
I can see what you're doing in your database and I have no idea whether it's the right approach or not. I am also aware that managing these sorts of relationships is difficult.
I'm wondering if a better approach would be to go right down to the atomic scale, and ask, what is the most fundamental thing? In answer to that question, then the most fundamental thing is a parent and child. Now there are some flies in the ointment, there is adoption, there are bastards, there's divorce, there are loads of ways of looking at, and describing these relationships. However I can't find an example which breaks the fundamental rule >>> "Parent Child". Even in the case of adoption, you have a "biological parent" and then you have an "adopting parent", you still have the relationship Parent Child...Rule, not Broken!
Now it becomes a simple many to many table on the left you have "parent" and on the right you have "child"... All you need is a "person table" in which every person is given a unique ID. You can add these ID's to the many to many table. If the person is a parent, their ID would go on the left and the ID of their child/children would go on the right. The "Parent" is also a child, and in this case, their ID would go on the right and their parents ID would go on the left.
From this basic information it would is possible to identify siblings, cousins, and derive all of the other relationships. It might be necessary to add an extra field which would identify a parent as being biological or adoptive, but I'm not sure.
I've got no idea if this is the right approach but my instinct is it's worth a go.
I would say this has very likely been done before so it might be worth having a look around to see if you can find an original Wheel, before you "reinvent the wheel"...
I recently did a couple of videos HERE on "many to many" relationships. I think you've got that covered, but I thought, what the hell, any excuse to promote my videos!
I'm wondering if a better approach would be to go right down to the atomic scale, and ask, what is the most fundamental thing? In answer to that question, then the most fundamental thing is a parent and child. Now there are some flies in the ointment, there is adoption, there are bastards, there's divorce, there are loads of ways of looking at, and describing these relationships. However I can't find an example which breaks the fundamental rule >>> "Parent Child". Even in the case of adoption, you have a "biological parent" and then you have an "adopting parent", you still have the relationship Parent Child...Rule, not Broken!
Now it becomes a simple many to many table on the left you have "parent" and on the right you have "child"... All you need is a "person table" in which every person is given a unique ID. You can add these ID's to the many to many table. If the person is a parent, their ID would go on the left and the ID of their child/children would go on the right. The "Parent" is also a child, and in this case, their ID would go on the right and their parents ID would go on the left.
From this basic information it would is possible to identify siblings, cousins, and derive all of the other relationships. It might be necessary to add an extra field which would identify a parent as being biological or adoptive, but I'm not sure.
I've got no idea if this is the right approach but my instinct is it's worth a go.
I would say this has very likely been done before so it might be worth having a look around to see if you can find an original Wheel, before you "reinvent the wheel"...
I recently did a couple of videos HERE on "many to many" relationships. I think you've got that covered, but I thought, what the hell, any excuse to promote my videos!
Last edited:
- Local time
- Today, 23:23
- Joined
- Feb 19, 2013
- Messages
- 16,649
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
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
