4 Replies Latest reply on May 7, 2012 9:59 PM by Guy_Smith

# Can't get a Sub-Summary Calculation to Calculate Correctly

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:

LUNCHEON_VENUE_NAME

Riverside

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

etc…

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))

and

- 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.

Thanks!

Guy

• ###### 1. Re: Can't get a Sub-Summary Calculation to Calculate Correctly

It sounds like it is not calculating based on the subsummary, but on totals.  If you can post your file it would be easier to find the problem.

I don't know that I would base that calculation on a Summary, but instead on relationships that way you can hold it to just that table.

1 of 1 people found this helpful
• ###### 2. Re: Can't get a Sub-Summary Calculation to Calculate Correctly

If I understand correctly, I think you can get there with straight-forward summary fields.

Sum_TableTotal = Total of FoodCost

Sum_IndShare = Average of FoodCost

Put both fields in subsummary part sorted by Table.

Craig

• ###### 3. Re: Can't get a Sub-Summary Calculation to Calculate Correctly

Craig:  Your solution worked perfectly!  I don’t know why I didn’t try another summary field beings the first two worked as expected, but thanks much for picking up on it so quickly and getting me back on track.

Best regards,

Guy

• ###### 4. Re: Can't get a Sub-Summary Calculation to Calculate Correctly

DigitalCarpenter:  Thanks for the insight:  I've never tried to use multiple relationships to further define my record set - from what I can gather, they act like super-strength finds, but at my current level (Noobius Extremus), I can't figure out the second relationship:  The first relationship is the Primary Key to Foreign Key, but what could I use for the further defining relationship?  This approach would give me a Universal solutiojn that I could use in many cases and a Strategic answer is usually more flexible that a tactical answer.  I'd sure appreciate any thoughts you might have?

Best Regards,

Guy

p.s.  The panoramas on your web site are awesome!  Thanks for sharing those as well!