Group Totals in Report

[JWeaver]

I have a report that lists the total number of hours worked by each employee
for a particular program. Some employees work in more than one program. My
report gives me the correct totals for each program, however, I want to add a
grand total of the hours worked for all programs. Is there a way to do this
so that the grand total can appear on the same line as the last program?

Right now, the grand total that appears is the same as the number of hours
in the total hours for program, which is OK if only one program is involved
but not when there are more than one.

Example of how report looks now:

Name Pgm Total Grand Total

Adams John W 40 40

Hall Lisa P 25 25
Hall Lisa Y 20 20

Williams Sally P 30 30
Williams Sally W 30 30
Williams Sally Y 20 20

Example of what I need report to look like:

Name Pgm Total Grand Total

Adams John W 40 40

Hall Lisa P 25
Hall Lisa Y 20 45

Williams Sally P 30
Williams Sally W 30
Williams Sally Y 20 80


Any help would be appreciated!!

 

[JWeaver]

OK. I created a Text Field and set its control source to the Sum of Hours
field. Then, I set the Running Sum to Over Group. Now, when I run the
report, I get the following output (it's getting close to what I want):

Name Pgm Total Grand Total

Adams John W 40 40

Hall Lisa P 25 25
Hall Lisa Y 20 45

Williams Sally P 30 30
Williams Sally W 30 60
Williams Sally Y 20 80

I don't want every running total to appear, only the last one when there is
more than one program being totaled, i.e., For Adams 40, Hall 45, and
Williams 80.

 

[J Sedoff]

I'm not well versed in Access, but you might try the Last() command in your
grand total column?

 

[JWeaver]

Thanks. I tried your suggestion but I get the grand total for the last
employee on my report.

Any other suggestions?

 

[J Sedoff]

Have you tried the "Sorting and Grouping" window? From there you can add a
Header/Footer for a field and in the footer you should be able to add
something like a =Sum([Total]) expression.

Hope that helps,
Jim

 

[Marshall Barton]

OK. I created a Text Field and set its control source to the Sum of Hours
field. Then, I set the Running Sum to Over Group. Now, when I run the
report, I get the following output (it's getting close to what I want):

Name Pgm Total Grand Total

Adams John W 40 40

Hall Lisa P 25 25
Hall Lisa Y 20 45

Williams Sally P 30 30
Williams Sally W 30 60
Williams Sally Y 20 80

I don't want every running total to appear, only the last one when there is
more than one program being totaled, i.e., For Adams 40, Hall 45, and
Williams 80.

To make a text box invisible on all but the group's last
record, you need a way to know when you are processing the
last record in the group.

This can be done by adding a text box (named txtDtlCnt) to
the group header section and setting its control source
expression to =Count(*)

Next, add a textbox (named txtLine) to the detail section.
Set its control source to =1 and RunningSum to Over Group

Now you can add a line of code to the detail section's
Format event procedure:
Me.[grand yotal text box].Visible = (txtLine=txtDtlCnt)

 

[JWeaver]

Thanks Marshall, that worked for me.

I posted another question that refers to this same field. When I try to add
conditional formatting to it I get a #Name? error in every field. Any idea
why this happens? I want this field to be highlighted whenever the total is
greater than 80. Conditional formatting works fine in another field that I
have on this report but not for this one.

--
JWeaver

OK. I created a Text Field and set its control source to the Sum of Hours
field. Then, I set the Running Sum to Over Group. Now, when I run the
report, I get the following output (it's getting close to what I want):

Name Pgm Total Grand Total

Adams John W 40 40

Hall Lisa P 25 25
Hall Lisa Y 20 45

Williams Sally P 30 30
Williams Sally W 30 60
Williams Sally Y 20 80

I don't want every running total to appear, only the last one when there is
more than one program being totaled, i.e., For Adams 40, Hall 45, and
Williams 80.

To make a text box invisible on all but the group's last
record, you need a way to know when you are processing the
last record in the group.

This can be done by adding a text box (named txtDtlCnt) to
the group header section and setting its control source
expression to =Count(*)

Next, add a textbox (named txtLine) to the detail section.
Set its control source to =1 and RunningSum to Over Group

Now you can add a line of code to the detail section's
Format event procedure:
Me.[grand yotal text box].Visible = (txtLine=txtDtlCnt)

 

[Marshall Barton]

Thanks Marshall, that worked for me.

I posted another question that refers to this same field. When I try to add
conditional formatting to it I get a #Name? error in every field. Any idea
why this happens? I want this field to be highlighted whenever the total is
greater than 80. Conditional formatting works fine in another field that I
have on this report but not for this one.

Sounds strange that all the controls are displaying #Name,
but that usually means Access can not resolve some name
somewhere, probably in the CF expression.

One thought about that issue is that CF require's either
fully qualified references to controls or the control names
must be in [ ]