Running Balance for an Unbound field

[MarieT]

I have an unbound commission rate textbox (field) in a report,where the
commission rate value is determined in the "On Format" event, depending on
the values in some of the record fields. This commsion_rate field is then
used to calculate the commission amount textbox in the report. My problem
comes when I try to sum the commission amount at the group level. Since the
rate is unbound and determined at run-time in the "On Format" event procedure
using VBA, I can't use Sum([amount] *[rate]). I used code in VBA to add a
running balance for the group total. In the "On Format" event procedure,
after I determine the commission rate to use, I set the total_text_box to the
(rate * Amount + total_text_box). This works only if the group does not
extend beyond 1 page. When a group extends beyond one page, The total
commision amount for the group is more than it should be.
I researched the problem and found out that the On Format event may occur
more than one time across pages. I tried using the On Retreat event to
deduct the amount that was added in the On Format event, but since my
KeepTogether property is set to No, the ON Retreat event is not even
triggered: I put a breakpoint to see if this event is called, and it does not
get called unless I set the Keeptogether property for the group to Yes. In
such case, the total is even more dramatically wrong.
I don't know how to solve this problem. I am desperate at this point! The
commission amount at the detail record level is correct. The sum at the
group level is incorrect when the group extends beyond one page. I would
really appreciate your help. Please Help!

 

[Allen Browne]

Marie, there is no reliable way to use the report events to arrive at these
calculations.

You can avoid some of the problems by using the Print event instead of the
Format event, but there is no way I would trust that either. There are still
problems with rolling over pages, especially if you don't preview/print
every page of the report. For example, if you preview the report, and jump
to page 5, the events for the intervening pages pages don't fire, so the
totals are not collected correctly.

The Running Sum does work reliably. Is there any possibility you could add a
group header and calculate the rate there for the coming detail items?

Commission payments can certainly get complex, with sliding scales, bonuses,
and competitions that can each span different time frames. Personally, I
think that trying to calculate them in a report is not going to give the
results you need. It might be better to create a pair of tables--Commission
and CommissionDetail--to store the commission payment and the details of the
items in that payment (i.e. how it is derived.) This involves writing code
to loop through all the sales (hopefully with a recordset populated by a
GROUP BY query that does part of the work), and calculate the items the
commission payment covers, probably flagging those items so you know the
payment has been calculated and locking them so that no further
edits/deletes are possible on those records once the commission has been
calculated. The end result is:
- a very easy report (read from Commission and CommisionDetail);
- verifiable proof of how the payment was derived, and exactly which items
are included;
- a consistent, repeatable store of what payments where made when and to
whom;
- the opportunity to create reversals and adjustments in furture months
(e.g. if a sale was originally attibuted to the wrong person);
- the chance to undo the last batch and regenerate it, while still producing
a complete history trail of how the payments are derived.

It does take a bit of code to do this, but it is the only way I would
consider developing something like that.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound commission rate textbox (field) in a report,where the
commission rate value is determined in the "On Format" event, depending on
the values in some of the record fields. This commsion_rate field is then
used to calculate the commission amount textbox in the report. My problem
comes when I try to sum the commission amount at the group level. Since
the
rate is unbound and determined at run-time in the "On Format" event
procedure
using VBA, I can't use Sum([amount] *[rate]). I used code in VBA to add a
running balance for the group total. In the "On Format" event procedure,
after I determine the commission rate to use, I set the total_text_box to
the
(rate * Amount + total_text_box). This works only if the group does not
extend beyond 1 page. When a group extends beyond one page, The total
commision amount for the group is more than it should be.
I researched the problem and found out that the On Format event may occur
more than one time across pages. I tried using the On Retreat event to
deduct the amount that was added in the On Format event, but since my
KeepTogether property is set to No, the ON Retreat event is not even
triggered: I put a breakpoint to see if this event is called, and it does
not
get called unless I set the Keeptogether property for the group to Yes.
In
such case, the total is even more dramatically wrong.
I don't know how to solve this problem. I am desperate at this point!
The
commission amount at the detail record level is correct. The sum at the
group level is incorrect when the group extends beyond one page. I would
really appreciate your help. Please Help!

 

[MarieT]

Thank you for your help Allen. I actually solved my problem by using a
hidden running balance field in the detail section. In the Group footer, I
have a text box that has the hidden running balance field as its
ControlSource. I do the same for the other group level in the hierarchy.
The Running Sum feature does seem to work reliably as you mentioned. I am
very grateful, Thank You!
Marie

Marie, there is no reliable way to use the report events to arrive at these
calculations.

You can avoid some of the problems by using the Print event instead of the
Format event, but there is no way I would trust that either. There are still
problems with rolling over pages, especially if you don't preview/print
every page of the report. For example, if you preview the report, and jump
to page 5, the events for the intervening pages pages don't fire, so the
totals are not collected correctly.

The Running Sum does work reliably. Is there any possibility you could add a
group header and calculate the rate there for the coming detail items?

Commission payments can certainly get complex, with sliding scales, bonuses,
and competitions that can each span different time frames. Personally, I
think that trying to calculate them in a report is not going to give the
results you need. It might be better to create a pair of tables--Commission
and CommissionDetail--to store the commission payment and the details of the
items in that payment (i.e. how it is derived.) This involves writing code
to loop through all the sales (hopefully with a recordset populated by a
GROUP BY query that does part of the work), and calculate the items the
commission payment covers, probably flagging those items so you know the
payment has been calculated and locking them so that no further
edits/deletes are possible on those records once the commission has been
calculated. The end result is:
- a very easy report (read from Commission and CommisionDetail);
- verifiable proof of how the payment was derived, and exactly which items
are included;
- a consistent, repeatable store of what payments where made when and to
whom;
- the opportunity to create reversals and adjustments in furture months
(e.g. if a sale was originally attibuted to the wrong person);
- the chance to undo the last batch and regenerate it, while still producing
a complete history trail of how the payments are derived.

It does take a bit of code to do this, but it is the only way I would
consider developing something like that.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound commission rate textbox (field) in a report,where the
commission rate value is determined in the "On Format" event, depending on
the values in some of the record fields. This commsion_rate field is then
used to calculate the commission amount textbox in the report. My problem
comes when I try to sum the commission amount at the group level. Since
the
rate is unbound and determined at run-time in the "On Format" event
procedure
using VBA, I can't use Sum([amount] *[rate]). I used code in VBA to add a
running balance for the group total. In the "On Format" event procedure,
after I determine the commission rate to use, I set the total_text_box to
the
(rate * Amount + total_text_box). This works only if the group does not
extend beyond 1 page. When a group extends beyond one page, The total
commision amount for the group is more than it should be.
I researched the problem and found out that the On Format event may occur
more than one time across pages. I tried using the On Retreat event to
deduct the amount that was added in the On Format event, but since my
KeepTogether property is set to No, the ON Retreat event is not even
triggered: I put a breakpoint to see if this event is called, and it does
not
get called unless I set the Keeptogether property for the group to Yes.
In
such case, the total is even more dramatically wrong.
I don't know how to solve this problem. I am desperate at this point!
The
commission amount at the detail record level is correct. The sum at the
group level is incorrect when the group extends beyond one page. I would
really appreciate your help. Please Help!

 

[Sonya]

Thanks for posting this, MarieT - I was having issues with a total field in
the report footer and had to do just what you suggested here to get it to
work.

Thank you for your help Allen. I actually solved my problem by using a
hidden running balance field in the detail section. In the Group footer, I
have a text box that has the hidden running balance field as its
ControlSource. I do the same for the other group level in the hierarchy.
The Running Sum feature does seem to work reliably as you mentioned. I am
very grateful, Thank You!
Marie

Marie, there is no reliable way to use the report events to arrive at these
calculations.

You can avoid some of the problems by using the Print event instead of the
Format event, but there is no way I would trust that either. There are still
problems with rolling over pages, especially if you don't preview/print
every page of the report. For example, if you preview the report, and jump
to page 5, the events for the intervening pages pages don't fire, so the
totals are not collected correctly.

The Running Sum does work reliably. Is there any possibility you could add a
group header and calculate the rate there for the coming detail items?

Commission payments can certainly get complex, with sliding scales, bonuses,
and competitions that can each span different time frames. Personally, I
think that trying to calculate them in a report is not going to give the
results you need. It might be better to create a pair of tables--Commission
and CommissionDetail--to store the commission payment and the details of the
items in that payment (i.e. how it is derived.) This involves writing code
to loop through all the sales (hopefully with a recordset populated by a
GROUP BY query that does part of the work), and calculate the items the
commission payment covers, probably flagging those items so you know the
payment has been calculated and locking them so that no further
edits/deletes are possible on those records once the commission has been
calculated. The end result is:
- a very easy report (read from Commission and CommisionDetail);
- verifiable proof of how the payment was derived, and exactly which items
are included;
- a consistent, repeatable store of what payments where made when and to
whom;
- the opportunity to create reversals and adjustments in furture months
(e.g. if a sale was originally attibuted to the wrong person);
- the chance to undo the last batch and regenerate it, while still producing
a complete history trail of how the payments are derived.

It does take a bit of code to do this, but it is the only way I would
consider developing something like that.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have an unbound commission rate textbox (field) in a report,where the
commission rate value is determined in the "On Format" event, depending on
the values in some of the record fields. This commsion_rate field is then
used to calculate the commission amount textbox in the report. My problem
comes when I try to sum the commission amount at the group level. Since
the
rate is unbound and determined at run-time in the "On Format" event
procedure
using VBA, I can't use Sum([amount] *[rate]). I used code in VBA to add a
running balance for the group total. In the "On Format" event procedure,
after I determine the commission rate to use, I set the total_text_box to
the
(rate * Amount + total_text_box). This works only if the group does not
extend beyond 1 page. When a group extends beyond one page, The total
commision amount for the group is more than it should be.
I researched the problem and found out that the On Format event may occur
more than one time across pages. I tried using the On Retreat event to
deduct the amount that was added in the On Format event, but since my
KeepTogether property is set to No, the ON Retreat event is not even
triggered: I put a breakpoint to see if this event is called, and it does
not
get called unless I set the Keeptogether property for the group to Yes.
In
such case, the total is even more dramatically wrong.
I don't know how to solve this problem. I am desperate at this point!
The
commission amount at the detail record level is correct. The sum at the
group level is incorrect when the group extends beyond one page. I would
really appreciate your help. Please Help!