Group Totals

  • Thread starter Thread starter Thomas Kroljic
  • Start date Start date
T

Thomas Kroljic

I have a report that contains the following sections: detail, Group
Header/Footer1, and Group Header/Footer2. As my detail records are printed,
I have vba code behind the report that keeps a running total of a specific
field if such conditions are met. When the Group Footer2 section prints, I
use vba code to display this total (move to an unbound text box) and
reinitialize the memory variable. This appears to work except I noticed that
it doesn't under the following conditions: after the detail records are
printed, when it goes to print the Group Footer2 section, if it doesn't have
enough room, it's execute the vba code (display total and initialize memory)
twice. The first time is when the report finishes printing the detail lines
and determines that it can not fit the Group footer2 on the same page, and
again when the report goes to the next page. Problem is, when it does go to
the next page and attempts to display my totals, at this point the total is
0 because the first time it executed the event GroupFooter2_format and
realized it would not fit on the current page, it initialize the variable
holding the total.

Any help or suggestion in this matter would be greatly appreciated.

Thomas j. Kroljic
 
Thomas said:
I have a report that contains the following sections: detail, Group
Header/Footer1, and Group Header/Footer2. As my detail records are printed,
I have vba code behind the report that keeps a running total of a specific
field if such conditions are met. When the Group Footer2 section prints, I
use vba code to display this total (move to an unbound text box) and
reinitialize the memory variable. This appears to work except I noticed that
it doesn't under the following conditions: after the detail records are
printed, when it goes to print the Group Footer2 section, if it doesn't have
enough room, it's execute the vba code (display total and initialize memory)
twice. The first time is when the report finishes printing the detail lines
and determines that it can not fit the Group footer2 on the same page, and
again when the report goes to the next page. Problem is, when it does go to
the next page and attempts to display my totals, at this point the total is
0 because the first time it executed the event GroupFooter2_format and
realized it would not fit on the current page, it initialize the variable
holding the total.
Click to expand...


That is the expected behavior. Access processes each
section in whatever order and as often as it needs to
generate the report. Bottom line: you can not use event
procedures to calculate a value from multiplke records.

THere are a couple of ways to get the correct total. The
easiest is to use an aggregate function on a conditional
expression. Eg:
=Sum(IIf(<condition>, somefield, 0))

If your condition is too complex for that approach, do the
condition check in the deatil section's Format event and
store the value (or zero) in a detail text box and set the
text box's RunningSum property to Over Group. The group
footer text box can then display the group total by
referring to the running sum text box.

Then add another running sum text box to the Group1 footer
for the Group2 footer to display its total.
 
Marshall,
Thanks for the quick response. I'll try your suggestion and post the
results.
 
Marshall,
After trying a couple of things, I'm not so sure I understand what you
are suggesting.

The query that the report uses has, let say two tables: HeaderInv and
DetailInv

The report displays the detail records and their dollar amount.
Unfortunately, there is one field, Delivery Charge that exist in the
HeaderInv table for the entire invoice. I want to be able to display this
amount on the first line item and show nothing on any other line items that
get displayed for this invoice.

I initially thought I'd be able to keep track of the InvoiceNumber using
vba code. When the InvoiceNumber did not equal the memory variable holding
it, I would display the field (visible = true), move the amount to another
variable that I would later display within the GroupFooter2, and then move
the InvoiceNumber to the memory variable. If the next record's invoice
number matched the memory variable holding the InvoiceNumber, I would not
display the field (visible = false), and I would not add the amount to the
memory variable holding the group total.

Since the report (Access) has the ability to execute the GroupFooter2
event more than once, if it can not fit it on the current page, my logic
doesn't work.

I thought I'd be able to follow your suggestions but I quess I don't
understand what it is you are suggesting.

I hope the above scenario is clear and will help you understand my
dilema.

Thanks,
Thomas J. Kroljic
 
I think I might have misunderstood what you were trying to
do. My suggextion was to add up a column in the report
where individual values might be excluded according to some
condition.

Now it souds like it just a matter of formatting the report
so that the Delivery field displays on the first detail. To
do that just add the Delivery text box to the detail section
and set its HideDuplicates property to Yes.

OTOH maybe you are struggling with calculating the invoice
total in the in the footer section?? To do that use an
expression like =Sum(amount) + Delivery
 
Marshall,
I'll try your suggestion tomorrow morning. I think the HideDuplicates
property might be the trick. I'll let you know how I make out. Thanks again
for helping. Greatly appreciated.

Thomas j. Kroljic

Marshall Barton said:
I think I might have misunderstood what you were trying to
do. My suggextion was to add up a column in the report
where individual values might be excluded according to some
condition.

Now it souds like it just a matter of formatting the report
so that the Delivery field displays on the first detail. To
do that just add the Delivery text box to the detail section
and set its HideDuplicates property to Yes.

OTOH maybe you are struggling with calculating the invoice
total in the in the footer section?? To do that use an
expression like =Sum(amount) + Delivery
--
Marsh
MVP [MS Access]


Thomas said:
After trying a couple of things, I'm not so sure I understand what you
are suggesting.

The query that the report uses has, let say two tables: HeaderInv and
DetailInv

The report displays the detail records and their dollar amount.
Unfortunately, there is one field, Delivery Charge that exist in the
HeaderInv table for the entire invoice. I want to be able to display this
amount on the first line item and show nothing on any other line items that
get displayed for this invoice.

I initially thought I'd be able to keep track of the InvoiceNumber using
vba code. When the InvoiceNumber did not equal the memory variable holding
it, I would display the field (visible = true), move the amount to another
variable that I would later display within the GroupFooter2, and then move
the InvoiceNumber to the memory variable. If the next record's invoice
number matched the memory variable holding the InvoiceNumber, I would not
display the field (visible = false), and I would not add the amount to the
memory variable holding the group total.

Since the report (Access) has the ability to execute the GroupFooter2
event more than once, if it can not fit it on the current page, my logic
doesn't work.

I thought I'd be able to follow your suggestions but I quess I don't
understand what it is you are suggesting.

I hope the above scenario is clear and will help you understand my
dilema.

prints,
I noticed
that doesn't
have go
to total
is
Click to expand...
Click to expand...
 
Marshall,
I thought the HideDuplicates would do the trick but doesn't in my case.
If difference invoices have the same Delivery Charge amount, which they do
most of time, than the amount isn't being displayed even though they have
different Invoices numbers.

At this point, I think I'm either going to remove the Delivery Charge from
displaying it on the detail line or I might create a subform that displays
the Delivery Charge total on the group break.

Thank you for all of your suggestions and time. I really appreciate it.
For someone like me who works alone, it great to have folks like you and
forums like this to seek help and guidance.

Thank you,
Thomas J. Kroljic


Marshall Barton said:
I think I might have misunderstood what you were trying to
do. My suggextion was to add up a column in the report
where individual values might be excluded according to some
condition.

Now it souds like it just a matter of formatting the report
so that the Delivery field displays on the first detail. To
do that just add the Delivery text box to the detail section
and set its HideDuplicates property to Yes.

OTOH maybe you are struggling with calculating the invoice
total in the in the footer section?? To do that use an
expression like =Sum(amount) + Delivery
--
Marsh
MVP [MS Access]


Thomas said:
After trying a couple of things, I'm not so sure I understand what you
are suggesting.

The query that the report uses has, let say two tables: HeaderInv and
DetailInv

The report displays the detail records and their dollar amount.
Unfortunately, there is one field, Delivery Charge that exist in the
HeaderInv table for the entire invoice. I want to be able to display this
amount on the first line item and show nothing on any other line items that
get displayed for this invoice.

I initially thought I'd be able to keep track of the InvoiceNumber using
vba code. When the InvoiceNumber did not equal the memory variable holding
it, I would display the field (visible = true), move the amount to another
variable that I would later display within the GroupFooter2, and then move
the InvoiceNumber to the memory variable. If the next record's invoice
number matched the memory variable holding the InvoiceNumber, I would not
display the field (visible = false), and I would not add the amount to the
memory variable holding the group total.

Since the report (Access) has the ability to execute the GroupFooter2
event more than once, if it can not fit it on the current page, my logic
doesn't work.

I thought I'd be able to follow your suggestions but I quess I don't
understand what it is you are suggesting.

I hope the above scenario is clear and will help you understand my
dilema.

prints,
I noticed
that doesn't
have go
to total
is
Click to expand...
Click to expand...
 
Thomas said:
I thought the HideDuplicates would do the trick but doesn't in my case.
If difference invoices have the same Delivery Charge amount, which they do
most of time, than the amount isn't being displayed even though they have
different Invoices numbers.
Click to expand...

I knew that! ;-\

At this point, I think I'm either going to remove the Delivery Charge from
displaying it on the detail line or I might create a subform that displays
the Delivery Charge total on the group break.
Click to expand...

The old standby technique is to add another text box named
txtLine. Set its control source to =1 and Running Sum to
Over Group.

Then add a line of code to the detail section's Format
event:

Me.txtDelivery.Visible = (Me.txtLine = 1)

Thank you for all of your suggestions and time. I really appreciate it.
For someone like me who works alone, it great to have folks like you and
forums like this to seek help and guidance.
Click to expand...

I can relate to that ;-)
 
Unfortunately, there is one field, Delivery
Charge that exist in the HeaderInv table
for the entire invoice. I want to be able to
display this amount on the first line item
and show nothing on any other line items
that get displayed for this invoice.
Click to expand...

You can work with this "the Access Way" if you show this value in either the
Header or the Footer. You will create work, stress, and difficulty for
yourself if you try to show a header item in just one
detail line. (From later responses, it appears you have discovered some of
that work, stress, and difficulty already.) And, logically, if the delivery
charge applies to the order/invoice as a whole, it should, logically, be
shown in the header or footer, which apply to the order as a whole.

"HeaderInv" table? That sounds suspciously as if you are creating tables
for each sub-item of output, which is possible, but unlikely, to lead to a
good database design.

Larry Linson
Microsoft Access MVP
 
Back
Top