I have a large group of people who have lunches throughout the year. At each luncheon venue, the people are placed into random, table-sized groups and then each table-sized group selects two or three people from their group to buy and prepare lunch for their table. To ensure the costs are equally shared by all diners at each table, the total cost of the food purchases are divided among all table participants.
Calculating the ‘Fair Share Cost’ should be simple. Just not for me!
I have a join table between the PEOPLE and LUNCHES tables (called LUNCHEON_VENUE) where I have the table group names and how much each person at each table paid for lunch ingredients. I also have a sub-summary for that table to get the number of people at each table, the total cost of food purchased, and the fair share cost for each person at the table. Typical field entries could be:
NAME DINING_TABLE FOOD_COST
Joe A $30
Karen A $10
Mike A $0
Susan A $0
Alice B $0
Pete B $0
Sarah B $39
When I sort by the DINING_TABLE field, everything looks right except for the fair share cost: The summary fields
- NUMBER_OF_DINERS (= Count Of (::LUNCHEON_VENUE_NAME & DINING_TABLE))
- TOTAL_COST_OF_FOOD_PURCHASED (=Total Of(FOOD_COST))
are displaying correctly, but the calculation field
FAIR_SHARE_COST (=TOTAL_COST_OF_FOOD_PURCHASED / NUMBER_OF_DINERS,
also in the subsummary, is calculating the per-person cost for the entire group of all diners at all dining tables. In the above example, each person dining at Table ‘A’ should owe $10 each ($40 total spent among 4 diners) and each person dining at Table ‘B’ should owe $13 each ($39 total spent among 3 people). Unfortunately, my calc is coming up with every person at the venue owing $11.29 ($79 total spent among 7 diners). Weird! Both parts of the calc equation are correct individually, but an incorrect result is displayed when they are used together in the calc.
Sorry about the long-winded explanation, but I hope I gave you enough info to figure out where I’m going wrong. Any help would be greatly appreciated.