Could really use some help in setting up one of my first complex data models. I have examples for illustrative detail. Essentially I need to consolidate information from our donor database CRM in ‘households’ and be able to roll up that information to total $ amounts as a unit at the same level as non-consolidated or complex models. Our database allows us to have contacts as one entity with unique CON # and organizations as a separate entity with unique ORG #.
I have a file where I would like to consolidate household accounts that combine CON # and ORG #s and I need to roll up those totals.
e.g. “Smith Family” is the consolidated household Group Identifier and includes:
CON 123 Bob Smith
CON 456 Janet Smith
ORG 123 Bob’s Company
ORG 456 Janet’s Company
I have a table with all of the unique CON #s and a table with all the unique ORG #s. Then I have a “consolidated table” structured with all the CON#s and ORG #s that make up a household.
What is the best way to model the data in order to have consolidated households roll up $ information at the same level as non consolidated households?