Easy answer: You want to designate {NumberOfDonors} in the [Individual] table as a count field that counts {MonthlyDonors}; {TotalAmount} will be a rollup field that follows the link in {MonthlyDonors} to roll up {Amount} using an aggregation formula of SUM(values). Here’s a quick demo base (open the link and copy the base to your own workspace to be able to examine it):
Not-so-easy answer: The same as the easy one, except that if by “monthly” you really do mean monthly, it gets more complicated. In that case, you’ll need to implement a method for breaking down donations by month and tallying them separately; you could do this by, say, creating a [MonthlyDonors] table for each month, with separate rollup fields in [Individuals] for each monthly table; alternatively, you could do some fancy calculating in [MonthlyDonors] to indicate for which month[s] a donation should be counted. If you’re trying to keep cumulative tabs on a fundraiser’s total YTD donations, things become even trickier, as you need to track annual donations on a prorated basis. (Since you are tracking ‘projected’ yearly amount, you may not be looking at things with such a degree of granularity…)
Alternatively, you could just clone the base on the first of each month, delete all the records in [MonthlyDonors], and start over. :winking_face:
There are relatively straightforward solutions to however you wish to approach this — and whatever ‘this’ is you want to approach — but the details depend on what you need to accomplish.
