Question on creating tables linking parent/child/spouse info (1 Viewer)

[nysimonsez]

Right now I have 2 tables in a database, 1 to collect personal info, the other for cash received from each person:

Table 1: NameID, address, etc...
Table 2: Date, NameID, amount

I want to add 2 functions to the database:

1. If I run a cash received report for husband (nameid=1), it will
automatically include the wife's cash received if he has one (nameid=2,
for example) and print both on the same report. How can I build that
"relationship" between 2 nameid's to reference they are husband and
wife.

2. The personal info table will have relatives, for ex: nameid1=father,
nameid2=wife, nameid3=child, nameid4=nameid1's father, etc. How can I keep track of these "relationship" in the database? I've read other threads where I should create a parent/child relationship table, is that enough or do I have to create a father/grandfather relationship table also?

Thanks for any suggestions you may have.

 

[neileg]

You have a theoretically unlimited number of relations (though in practice people don't have an infinite number of children), certainly too many to manage with fixed fields in your record. You could create a new realtionship table that has three fields, one for the ID of the 'target' the second for the ID of the relation and the third for the description of the relation.

Then, when you want to query on any individual, you can bring in the IDs of the related people from the relationship table.

That's the easy bit. The hard bit is going to be maintaining and managing the relationships. For example, you might be adding a woman who is the mother of three of your existing donors, the child of two, the sister of one, the grandmother of five, the wife of one, and the exwife of two. How are you going to do this unless you keep the family trees of all potential donors?

It's much easier when you are doing a parent child relationship because you always have two parents to each child so you're only tracking two relatives with fixed relationships.

 

[Pat Hartman]

Rather than keeping the actual relationships which requires a many-to-many relationship, you could just maintain a family group. To do this, add another column to Table1 called FamilyID. In this field, place the NameID of the "head of household". Then for reporting purposes, create a query and add Table1 to the query grid twice. Join the two tables by connecting the FamilyID of the first table with the NameID of the second table.

Make FamilyID a required field. Populate it with the NameID as a default. So your own NameID becomes the default value for your FamilyID unless you choose a different value.