Summing a column in a report

[cinnie]

My report has a column of numbers. Below the column, I would like to show a
text box with the sum of these numbers. In Excel I would just use something
like "= Sum(C3:C56)", but I don't know how to do this in Access. My real
problem is that each report has a different number of rows. Any clues??
Thanks

 

[Al Campagna]

cinnie,
If you had an uncalculated bound text control named Price, with
numeric values...
= Sum(Price)
would yield the Sum of Price in any Group Footer, or Report Footer.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[John Spencer]

Use the report footer or a group footer (report footer if you want to sum the
entire report and group footer if you want to subtotal by groups of records)
and add a control to the footer with its control source set to sum the
relevant field. Something Like

ControlSource: =Sum([Amount])



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[cinnie]

thanks Al and John. I think the reason Control Source: = Sum([txtA])
doesn't work is that txtA is from a XTab query. It is calculated based on a
Count. Does that mean I can't use 'Sum([txtA])' to sum these values? thanks
again

 

[Duane Hookom]

You can Sum() numeric values from crosstab or other queries. Make sure you
are not attempting to sum in the Page Footer section.

I'm confused why you would name a column/field "txtA" when "txt" is
generally a prefix for either a text box or a field/variable storing a
text/string value.

If it is a text box, you can't Sum() a control.

--
Duane Hookom
MS Access MVP

thanks Al and John. I think the reason Control Source: = Sum([txtA])
doesn't work is that txtA is from a XTab query. It is calculated based on
a
Count. Does that mean I can't use 'Sum([txtA])' to sum these values?
thanks
again
--
cinnie

cinnie,
If you had an uncalculated bound text control named Price, with
numeric values...
= Sum(Price)
would yield the Sum of Price in any Group Footer, or Report Footer.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




.

 

[Marshall Barton]

My report has a column of numbers. Below the column, I would like to show a
text box with the sum of these numbers. In Excel I would just use something
like "= Sum(C3:C56)", but I don't know how to do this in Access. My real
problem is that each report has a different number of rows.

Be careful here. The word "field" and the word "control"
mean two different things. This is one of those times when
the difference between a control bound to a field and a
record source field is very important.

When using an aggregate function (Count, Sum, etc), you must
use FIELDs from the report's record source table/query. You
can not use a control on the report.

 

[cinnie]

Yes, that was it! What I had already done was consistent with the good
advice from all of the experts that replied. But still nothing worked. You
identified the key point - that the numbers being summed were derived from an
aggregate function, so the underlying query's field had to be used, not the
report's control. Thanks for a great insight.

 

[Marshall Barton]

Yes, that was it! What I had already done was consistent with the good
advice from all of the experts that replied. But still nothing worked. You
identified the key point - that the numbers being summed were derived from an
aggregate function, so the underlying query's field had to be used, not the
report's control. Thanks for a great insight.

You're welcome, but I see that Duane was making the same
point earlier.