Best way to create a Family relationship table?

[Sgt_Utz]

I am designing a database for a cemetery and working to create a relationship table to link customers who are buried in the same cemetery (father, son, etc.)



The plan is when having the customer form open I click a button "Create Relationship" which has a form popup with an option group including "Mother, Father, Son, Daughter, etc.".

When saving the relationship it creates 2 records (shown below in green). Customer 23's Mother is 35. Using VBA it determines that Customer 35's Son (referencing sex field) is 23. This ensures that when pulling up the Mothers record it automatically shows 23 as her son in the subform. A composite ID is used so record 2335 and 3523 is created so the relationship can't be duplicated.


The pic below is crudely made from excel and google images just for reference but shows the end goal.


I'd have to code it to delete 2 records in the relationship table for every relationship created if deleting the customer, so just wondering if this is best practice or if anyone knows of a better way to accomplish this?

Thanks!

[June7]

I don't know if there is better but maybe instead of recreating the wheel, look at OTS family tree apps.

IMO, family tree and manufacturing type dbs are the most difficult to build because of recursive relationships. Other db platforms such as SQLServer handle this better than Access.

Also, Interned is a misspelling of Interred.

[Micron]

Agree; not for the faint of heart. Allen Browne site has some info that might help

http://allenbrowne.com/ser-06.html
http://allenbrowne.com/AppHuman.html

[davegri]

Microsoft published a template for a genealogy DB that might provide some useful ideas.

MS_Genealogy_Database_01076524.zip

[Sgt_Utz]

Thanks everyone! So I'm not interested in building a full genealogy tree, just enough to show who's related to who in the cemetery so it shows up on the subform. So you won't be able to create a tree from what I need.

I'll look into everything mentioned. Thanks!

[Micron]

I think you will find the principles are basically the same, the difference being that you won't likely need to go too deep. I can see how it might begin to approach such a level though, as
A marries B, both are in the cemetery
A & B had a child C who is also there
C had married D, and D is related to ...

I suppose the level of complexity depends on how far back you are going. It could be hundreds of years, which could certainly expand the connections.
Good luck with your research1

[isladogs]

Forum member @accesstos published a family relationships example database here a couple years ago. A forum search should locate it. Also try looking at the similar threads at the bottom of this page

[orange]

You may find this tutorial from Richard Rost helpful.
A related link.