A Calculation AND Relationship Conundrum: Where to post this one, Lee?

[Guy_Smith]

I am developing a solution to track funds for my scout troop and have a good portion of it up and running, but I’m getting stuck on how to handle meal charges when scouts go on an outing.

 

Basic concepts: 

- Many scouts can go on many different outings (Camping Trips, Skiing, Day Hikes, etc.) throughout the year. 

- For each outing, each scout is assigned to a specific Patrol, but those patrols change from outing to outing:  A particular scout may be assigned to Patrol #2 for one outing and to Patrol #4 for another.

- For each outing, each Patrol will have 3 or 4 scouts buy food for the 8-10 members of that patrol.  The total cost of all meals is equally divided between all scouts from that particular patrol and their account is either charged or credited depending on how much they paid for food, if any. 

 

Example:  Eight scouts are assigned to Patrol #1.  Jack brings food for breakfast that cost him $20.00.  Joe brought food for lunch that cost him $24.00, and John brought dinner that cost $30.00.  The remaining five scouts do not purchase any food.  After the outing is over, I calculate how much each scout’s fair share is – in this case it would be $74.00 / 8 scouts or $9.25 per scout – and then adjust their individual accounts accordingly:  The five scouts who did not bring any food are charged $9.25 each, Jack’s account is credited $20.00 minus the $9.25, or $10.75, Joe is credited $14.75, etc.  The scouts who bring the meals get to choose what to bring, so the fair share amounts vary greatly between patrols.

 

Right now, I am doing these calcs in Excel and then manually entering the charges/credits into the individual scouts’ accounts.  Elegant: No.  Efficient: No.  Error-prone: Oh, Yeah!

 

My two sticking points are: (1) How do I calculate each Patrol’s fair share, and (2) how do I get the credits/charges to automatically populate a new Income/Expense line item for each scout who attended the outing?

 

I think I have figured how to set up my Join Table to properly handle the many to many relationship between scouts and outings, but am stuck as to my next steps.  Thanks to all for wading through this entire post and I appreciate any comments/suggestions.

 

Best Regards,

 

Guy