How do I get data from another object which does not have a master-detail relationship?

Question

I'm working for a nonprofit. We have a customized object called Program Participation which has a lookup to the Contact object and also is the parent of three other objects. We want to create a field on the Account page for each school to count the number of students they have brought into our programs with several filters related to Program Participation object. There is no master-detail relationship between Account and Program Participation. And since the Program Participation object is already the parent of other objects, I was not able to create a master-detail relationship with Account object.

I tried to create a roll-up field on Account level and the drop-down summarized object list does not contain Program Participation object due to the lack of master-detail relationship. However, on school page, we do have 'program participation (School Attending)' in the related list, which we believe there is a way to retrieve data from another object although there is no master-detail relationship between them.

Answer 1

@AndrewFawcett built a great tool to do just what you are trying to achieve:

Declarative Rollups for Lookups!

Features Summary

• Rollup information between Lookup relationships not previously possible without writing Apex Triggers
• Define rollups using standard UI declaratively, no coding required
• Define filter criteria on rollups for example Rollup Amount on Opportunity onto Account for Closed Won
• Supports Realtime, Scheduled and Developer API modes
• Open source, available in code and managed package form.
• Managed package has passed Salesforce Security Review and is Aloha enabled (does not consume app, tab limits)
• NEW Supports Custom Metadata, rollups can be included in Change Sets and Packages for easier deployment

Answer 2

As @Adrian mentioned already, Declarative Rollups above is an excellent tool.

There is also an app on the app exchange called Rollup Helper that also works well in this situations

https://appexchange.salesforce.com/listingDetail?listingId=a0N30000009i3UpEAI