I have a family tree and want to total all wealth for the entire family tree for any selected family member

10 pts.
Tags:
SAP
SQL statements
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    What about spouses? -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following