One way is to create a self-relationship from the Season field to the Season Field. Then have a calculation:
SeasonTotal = Sum ( SelfBySeason::ContributionAmount )
Each record will show the total for the same season as it is.
If you want it for that season for that person, then make the relationship by Season and By PersonID.
I didn't clarify my initial statement very well. Season is a text field in Contributions.
I have tried to find more infomation on using "self" statements, but I've not been successful. Can you give me more info on a self-relationship?
Let me know if I should provide more information.
It is ok that Season's is a text field.
Create another Table Occurance of the table Contributions.
Rename it as contributions_SelfByContactSeason
Create another relationship between the Contribution Table and that new Table Occurance
Join the two by:
Contribution::ContactID = contributions_SelfByContactSeason::ContactID
Contribution::Season = contributions_SelfByContactSeason::Season
Then create the calculation:
SeasonTotal = Sum ( contributions_SelfByContactSeason::ContributionAmount )
Each record will then show the total for the same season and contact as it is for.
That got it!
And as usual, one solution creates another needed issue. I have a report to show:
Contact Name | Season | Total Amount |
If I use the Contacts Table, it only shows ONE season for each contact.
If I use the Contributions Table, it shows ALL Contributions (meaning that each lesser contribution registers in the report but the Total Amount is now listing TWICE).
Any thoughts on how I should proceed to remedy this report?
Really appreciate your help. I saw your response to my other issue. I'll be working on that shortly.
Find all records for 2012, say. Have a layout in the Contributions table, sort by Contact, have a Subsummary by Contact, remove the body part, and show that field in the sub-summary.