I'm helping set up a db that involves family members. I have created the relationships as follows:
The Contacts table has the individuals (kids included) with individual level data and a pkID.
Families has an address and pkID.
FamilyContacts has fk for contacts and families as well as a field for role (father,mother,child) and a field for holding the family name so it can be looked up on a layout.
What I am struggling with is:
1) I want to create a "look up" field that takes the last name and first names of the mother and father from the contacts table as they are created. i.e. I add Bob Smith and his address, then I add Jane Smith and look up (Smith, Bob) in the FamilyLookUp value list. Finally when I add the kids I look up Smith and the value list is now (Smith, Bob/Jane). This way there is no confusion when linking families with the same last name.
2) I want to display all the names of the linked family members in the contacts layout. It seems like this should be a calculation list field in the FamilyContacts table but I am sort of at a loss. My preference is to have the list be Father: (name), Mother: (name) and so on but left blank if one does not exist.
I'm assuming the fields for both of these will need to go in my join table but any insight or clarification would be much appreciated.