I have a database of a family tree of several separate families. Family of Joe smith, family of John Jones. etc. some families have only 1 member others have hundreds of children, grandchildren, great-grandchildren, etc.
John Jones had no children so he is a family of 1
John smith had 2 children S-C1 and S-C2
then S-C1 had 3 children S-C1-c1 and children S-C1-c2 and children S-C1-c3
then S-C2 had 2 children S-C2-c1 and children S-C2-c2
Each person has some info about them like their net wealth.
I want to see the total family net wealth
The database structure is
Other data about that person
So Joe smith =
personID = 1
parentID = 0 (0 indicates that they have no known parent, they are the 1st generation the top parent the root relative)
S-C1 (child of Joe Smith) =
personID = 2
parentID = 1 (his parent is #1 = Joe smith)
S-C1-c1 (child of S-C1 also the grandchild of Joe Smith if you follow up the tree)
personID = 10
parentID = 2 (his parent is #2 =S-C1)
So what I want to do is if any person is selected lets say S-C1 then I want to total assets of the entire family. (ie assets of Joe smith + assets of S-C1 + assets of S-C1-c1 )
So if any person in the SAME family is selected the result would be the same because if Joe smith is selected the formula would be (ie assets of Joe smith + assets of S-C1 + assets of S-C1-c1 ), If S-C1 is selected the formula would be (ie assets of Joe smith + assets of S-C1 + assets of S-C1-c1 ), if S-C1-c1 is selected the formula would be (ie assets of Joe smith + assets of S-C1 + assets of S-C1-c1 )
How do I do that with a single Microsoft Access SQL statement?
Free Guide: Managing storage for virtual environments
Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!