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 personID parentID 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?

Software/Hardware used:
Microsoft Access