You are trying to do this as a circular reference via relationship rather than use the natural abilities of the base table.
You have Items as the body.
You then have either leading and/or trailing summary parts for each ot the following:
... and on each part you place the summary of cost field... yes the same field on every one... and in the trailing grand summary.
WHEN you sort it in the same order it will break down the costs for each of your summary parts. You do not have to refer back to any table for anything other than to place a field for display/print purposes.
Thanks for your reply Lyndsay. I provided a somewhat simple problem, hoping that I wouldn't need to dwelve into greater complexity, but I think we need to do it. Sorry for not being more clear. We also have a Payments table, and a PayITEMS table (so as to allow a many-to-many relationship between Payments and Expenses).
Expenses::Subtotal_cn = Sum (Expenses_Items::Cost_n)
Expenses::TaxRate_n = a number
Expenses::Total_cn = Expenses::Subtotal_cn * Expenses::TaxRate_n
Expenses::Total_sum = Summary field totalling Total_cn
Expenses::Payments = Sum(PayITEMS::Amount_n)
Expenses::Payments_sum = Summary field totalling Expenses::Payments
Expenses::Balance_cn = Expenses::Total_cn - Expenses::Payments
Expenses::Balance_sum = Summary field totalling Expenses::Balance_cn
I can actually pull the total back to the Items table by doing this:
Items::Cost_n = a number
Items:CostPlusTax = Items::Cost_n * Expenses::TaxRate_n
And by doing this, the values to cost in the subsummaries work.
The problem is displaying the correct Balance, since the Payments are applied to Expenses as a whole.
OK, I got it to work, though it looks very convoluted... Please let me know what you think:
Cost_cn + (Cost_cn * ExpenseTMS_Expenses::TaxRate)
Total of CostPlusTax_cn
Sum(ExpenseTMS_Expenses_PayTMS::Amount_n) / ExpenseTMS_Expenses::Total_cn * CostPlusTax_cn
Total of Payments_cn
(Cost_cn + (Cost_cn * ExpenseTMS_Expenses::TaxRate))
(Sum(ExpenseTMS_Expenses_PayTMS::Amount_n) / ExpenseTMS_Expenses::Total_cn * CostPlusTax_cn)
Total of Balance_cn