Column totals in Access

[Higgie]

Please help. I'm stumped.

I have a report with three columns of data calculated from a fourth column.
I can't get them to total in the report footer. I keep getting a prompt for
a parameter value. The report looks something like this:

Monthly Annual 80% 20%
100 1200 960 240
80 960 768 192

180 2160 ? ?

The first column totals fine. The second column totals fine. I can't get
the other two which are calculations based on the second to total.

Carlton

 

[Wayne Morgan]

You have a few options. I assume you are using a Sum() function to get the
Monthly and Annual totals in the footer. This works because you have a field
to Sum, but I assume that the 80% and 20% are calculated textboxes, so there
is no field associated with them.

1) Assuming that 80% and 20% are calculated textboxes on the report, you
could add a copy of each of these, set the Visible property of the copy to
No and set the Running Sum property to Over All. These copies of the
textboxes will then sum the records for you. In the footer, just set the
textbox's Control Source to the name of the summing copy of the associated
textbox.

Example:
=txt80PercentSum

2) You could do the 80% and 20% calculation in the query feeding the form
using calculated fields. You could then use the names of these calculated
fields and use a Sum() expression in the footer's textboxes.

3) You could use calculations in the footer's textboxes and calculate 80%
and 20% of the textbox in the footer that has the sum of the Annual column.
You have a potential for round-off errors doing it this way. 80% of the
total may not be quite the same as the sum of the 80% values in the column.

 

[Higgie]

Wayne, I've tried your suggestion of copying calculated text boxes and
selecting Running Sum of Over All but the box only picks up the last record,
not the sum of the column. ???

 

[Duane Hookom]

Don't use any code or running sums. Just Sum() the control source from your
last two columns. Since you didn't provide these expressions, my WAG is:
=Sum([Annual] * 0.8)
and
=Sum([Annual] * 0.2)

 

[Wayne Morgan]

To use the running sum textboxes, they need to be in the same section of the
report as the current textboxes that show 80% and 20%. You probably don't
want these to show, hence setting their Visible property to No. Their
control source should be identical to the 80% and 20% textboxes also. Then,
in the footer, set the "total" textbox for that column to the name of the
running sum textbox.

Example:
=[NameOfRunningSumTextboxInDetailSection]

 

[R Marko]

How can you add the two columns in the report footer? I have a similar
situation where I'm trying to add
(sum[Annual]*0.8 plus sum[Annual]*0.2
How would I do that?

Don't use any code or running sums. Just Sum() the control source from your
last two columns. Since you didn't provide these expressions, my WAG is:
=Sum([Annual] * 0.8)
and
=Sum([Annual] * 0.2)

--
Duane Hookom
MS Access MVP
--

Please help. I'm stumped.

I have a report with three columns of data calculated from a fourth
column.
I can't get them to total in the report footer. I keep getting a prompt
for
a parameter value. The report looks something like this:

Monthly Annual 80% 20%
100 1200 960 240
80 960 768 192

180 2160 ? ?

The first column totals fine. The second column totals fine. I can't get
the other two which are calculations based on the second to total.

Carlton

 

[Duane Hookom]

=Sum([Annual])*0.8 + Sum([Annual])*0.2
or in this case:
=Sum([Annual])

--
Duane Hookom
MS Access MVP
--

How can you add the two columns in the report footer? I have a similar
situation where I'm trying to add
(sum[Annual]*0.8 plus sum[Annual]*0.2
How would I do that?

Don't use any code or running sums. Just Sum() the control source from
your
last two columns. Since you didn't provide these expressions, my WAG is:
=Sum([Annual] * 0.8)
and
=Sum([Annual] * 0.2)

--
Duane Hookom
MS Access MVP
--

Please help. I'm stumped.

I have a report with three columns of data calculated from a fourth
column.
I can't get them to total in the report footer. I keep getting a
prompt
for
a parameter value. The report looks something like this:

Monthly Annual 80% 20%
100 1200 960 240
80 960 768 192

180 2160 ? ?

The first column totals fine. The second column totals fine. I can't
get
the other two which are calculations based on the second to total.

Carlton

 

[Ruptured Duck]

Option 1 worked beautifully for me, thanks. I had a column with an iif
function and I wanted to sum that column of iif results.