I'm creating a database to track the donations of our organization members and their households. My goal is to be able to create an interactive Interface for our Board to see the percentage of giving households vs non-giving households in any particular month. For example, if 6 out of 10 households gave in October, I'd want to see 60%; if 3 households gave in December, I'd want to see 30%.
Here's how I have the base set up so far:
Donations: Every donation we receive is marked with the date received, the amount donated, the Member who donated (linked), and the Household the member belongs to (linked)
Members: We have 100 members, each listed in this table and each linked to their Donation (if they've donated) and a designated Household (below)
Households: For simplicity of illustration, I'll just say that we have 50 households (two Members in each Household record). Because all the records are linked above, each Household record shows the two linked Members and all the linked Donations.
My current solution is to create a rollup field in the Households table to find the sum of Donations in a specific month. Then I create another column and use an IF/THEN formula to identify "Giving" or "Non-Giving." Then I put that in a Pie Chart in the interface to show the percentages.
However, this means I have to repeat the process every single month and edit the Interface to reflect the new column. I'd love to have it all automated so the interface always shows the precise percentages AND a viewer could use the filter feature to select which month they'd like to see.
