I wish to determine a cumulative percentage for subform records. I can use
something like this as the control source of an unbound text box (formatted
as Percent) in the subform footer:
=([Forms]![MainForm]![Qty] - [SubQty]) / [Forms]![MainForm]![Qty]
where [Forms]![MainForm]![Qty] is a field from the main form's record
source, and [SubQty] is a field in the subform record. If the main form's
value is 100 and the subform's value is 10 I need the result to be 90%,
which it is. However, if there are two subform records, each with the same
value, then the percentage needs to be 90% * 90%, or 81%. As it stands the
textbox in the footer calculates the first
I could get the same result for a single record using VBA or a calculated
query field. I'm not particular about how to go about this, but I can't
discover a way of producing the desired result. If there was a Product
function in the same way there is a Sum or Avg function it would probably do
the trick, but if there is such a thing it is not called Product, nor any
other name that occurs to me.
|
Similar Threads
