Summing a column on a report

[Guest]

I have created a report that I need to put the grand total at the bottom of
for two of the columns. What I have done is put two unbound text boxes in
the footer aligned under the columns I need to sum. In the control source
for each of these boxes I have typed =Sum([Amount Payment Type 1]) in one and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers are being
pulled from have the same exact name). When I view the report in layout view
I do not see the total of the column above, but #Error instead. What did I
do wrong?

 

[Duane Hookom]

What do you mean by "the footer"? There are Report, Page, and Group Footers.
I expect you might have chosen the Page footer which will not work.

 

[fredg]

I have created a report that I need to put the grand total at the bottom of
for two of the columns. What I have done is put two unbound text boxes in
the footer aligned under the columns I need to sum. In the control source
for each of these boxes I have typed =Sum([Amount Payment Type 1]) in one and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers are being
pulled from have the same exact name). When I view the report in layout view
I do not see the total of the column above, but #Error instead. What did I
do wrong?

You're probably using the Page Footer for this calculation.
See Microsoft KnowlegeBase article:
132017 "How to sum a column of numbers in a report by page"

 

[Guest]

Yes, it is the page footer. That is where I thought it should go. How do I
find the group footers?

What do you mean by "the footer"? There are Report, Page, and Group Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the bottom of
for two of the columns. What I have done is put two unbound text boxes in
the footer aligned under the columns I need to sum. In the control source
for each of these boxes I have typed =Sum([Amount Payment Type 1]) in one
and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers are
being
pulled from have the same exact name). When I view the report in layout
view
I do not see the total of the column above, but #Error instead. What did
I
do wrong?

 

[Duane Hookom]

I think you want to use the Report Footer. To create Group Footers, you must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go. How do
I
find the group footers?

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the bottom
of
for two of the columns. What I have done is put two unbound text boxes
in
the footer aligned under the columns I need to sum. In the control
source
for each of these boxes I have typed =Sum([Amount Payment Type 1]) in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead. What
did
I
do wrong?

 

[Guest]

Okay, got that going right finally, thank you so much! Now, can you tell me,
is it possible to calculate rows with the total to the right? Or can I do it
right in the table so that I can include it in a report?

I think you want to use the Report Footer. To create Group Footers, you must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go. How do
I
find the group footers?

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the bottom
of
for two of the columns. What I have done is put two unbound text boxes
in
the footer aligned under the columns I need to sum. In the control
source
for each of these boxes I have typed =Sum([Amount Payment Type 1]) in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead. What
did
I
do wrong?

 

[Duane Hookom]

"calculate rows with the total to the right" could mean a lot of different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather than
summing a single field across records, you should explain why you have your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you tell
me,
is it possible to calculate rows with the total to the right? Or can I do
it
right in the table so that I can include it in a report?

I think you want to use the Report Footer. To create Group Footers, you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go. How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the
bottom
of
for two of the columns. What I have done is put two unbound text
boxes
in
the footer aligned under the columns I need to sum. In the control
source
for each of these boxes I have typed =Sum([Amount Payment Type 1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers
are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead.
What
did
I
do wrong?

 

[Guest]

What I have is something like the following:

Contact Name Payment Type 1 Payment Type 2

What the boss wants done is to total each column (ie - all of Payment Type
1) and have the total appear below the appropriate column (you already helped
me with this and I am all set with it). Then he wants to total for each
Contact Name the Payment Type 1 and Payment Type 2 amounts and have that
total appear to the right of the Payment Type 2 column for each Contact Name
given.

I have experimented and tried putting the same type of unbound box that
totals each column in the Details section of the report and what it does is
give me the total for the two column totals as opposed to each row on the
report.

Please let me know if I am unclear in any way and I will try to clarify.

"calculate rows with the total to the right" could mean a lot of different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather than
summing a single field across records, you should explain why you have your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you tell
me,
is it possible to calculate rows with the total to the right? Or can I do
it
right in the table so that I can include it in a report?

I think you want to use the Report Footer. To create Group Footers, you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go. How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the
bottom
of
for two of the columns. What I have done is put two unbound text
boxes
in
the footer aligned under the columns I need to sum. In the control
source
for each of these boxes I have typed =Sum([Amount Payment Type 1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the numbers
are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead.
What
did
I
do wrong?

 

[Duane Hookom]

Why don't you simply reply to my request "Can you provide some sample
records and desired display in the report?"
You provided some column names/titles and a description but not what I had
expected.
--
Duane Hookom
MS Access MVP

What I have is something like the following:

Contact Name Payment Type 1 Payment Type 2

What the boss wants done is to total each column (ie - all of Payment Type
1) and have the total appear below the appropriate column (you already
helped
me with this and I am all set with it). Then he wants to total for each
Contact Name the Payment Type 1 and Payment Type 2 amounts and have that
total appear to the right of the Payment Type 2 column for each Contact
Name
given.

I have experimented and tried putting the same type of unbound box that
totals each column in the Details section of the report and what it does
is
give me the total for the two column totals as opposed to each row on the
report.

Please let me know if I am unclear in any way and I will try to clarify.

"calculate rows with the total to the right" could mean a lot of
different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather
than
summing a single field across records, you should explain why you have
your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you
tell
me,
is it possible to calculate rows with the total to the right? Or can I
do
it
right in the table so that I can include it in a report?

:

I think you want to use the Report Footer. To create Group Footers,
you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go.
How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the
bottom
of
for two of the columns. What I have done is put two unbound text
boxes
in
the footer aligned under the columns I need to sum. In the
control
source
for each of these boxes I have typed =Sum([Amount Payment Type
1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the
numbers
are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead.
What
did
I
do wrong?

 

[Guest]

I truly appreciate your assistance with the problem I am having, but I feel a
little softer approach would go a long way. With the exception of actual
records, I thought I had given you what you asked for.

contact amount amount
name payment payment Total
type 1 type 2 Payment

Duane $1000.00 $2000.00 $3000.00
Teri $1000.00 $1000.00 $2000.00

Grand Totals $2000.00 $3000.00 $5000.00

With the exception of capitalization in the column titles (and our names),
the report should look just like the above. Is there a way I can attach the
report so you can actually see it?

Why don't you simply reply to my request "Can you provide some sample
records and desired display in the report?"
You provided some column names/titles and a description but not what I had
expected.
--
Duane Hookom
MS Access MVP

What I have is something like the following:

Contact Name Payment Type 1 Payment Type 2

What the boss wants done is to total each column (ie - all of Payment Type
1) and have the total appear below the appropriate column (you already
helped
me with this and I am all set with it). Then he wants to total for each
Contact Name the Payment Type 1 and Payment Type 2 amounts and have that
total appear to the right of the Payment Type 2 column for each Contact
Name
given.

I have experimented and tried putting the same type of unbound box that
totals each column in the Details section of the report and what it does
is
give me the total for the two column totals as opposed to each row on the
report.

Please let me know if I am unclear in any way and I will try to clarify.

"calculate rows with the total to the right" could mean a lot of
different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather
than
summing a single field across records, you should explain why you have
your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you
tell
me,
is it possible to calculate rows with the total to the right? Or can I
do
it
right in the table so that I can include it in a report?

:

I think you want to use the Report Footer. To create Group Footers,
you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go.
How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the
bottom
of
for two of the columns. What I have done is put two unbound text
boxes
in
the footer aligned under the columns I need to sum. In the
control
source
for each of these boxes I have typed =Sum([Amount Payment Type
1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the
numbers
are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead.
What
did
I
do wrong?

 

[Guest]

I truly appreciate your assistance with the problem I am having, but I feel a
little softer approach would go a long way. With the exception of actual
records, I thought I had given you what you asked for.

contact amount amount
name payment payment Total
type 1 type 2 Payment

Duane $1000.00 $2000.00 $3000.00
Teri $1000.00 $1000.00 $2000.00

Grand Totals $2000.00 $3000.00 $5000.00

With the exception of capitalization in the column titles (and our names),
the report should look just like the above. Is there a way I can attach the
report so you can actually see it?

Why don't you simply reply to my request "Can you provide some sample
records and desired display in the report?"
You provided some column names/titles and a description but not what I had
expected.
--
Duane Hookom
MS Access MVP

What I have is something like the following:

Contact Name Payment Type 1 Payment Type 2

What the boss wants done is to total each column (ie - all of Payment Type
1) and have the total appear below the appropriate column (you already
helped
me with this and I am all set with it). Then he wants to total for each
Contact Name the Payment Type 1 and Payment Type 2 amounts and have that
total appear to the right of the Payment Type 2 column for each Contact
Name
given.

I have experimented and tried putting the same type of unbound box that
totals each column in the Details section of the report and what it does
is
give me the total for the two column totals as opposed to each row on the
report.

Please let me know if I am unclear in any way and I will try to clarify.

:

"calculate rows with the total to the right" could mean a lot of
different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather
than
summing a single field across records, you should explain why you have
your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you
tell
me,
is it possible to calculate rows with the total to the right? Or can I
do
it
right in the table so that I can include it in a report?

:

I think you want to use the Report Footer. To create Group Footers,
you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go.
How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the
bottom
of
for two of the columns. What I have done is put two unbound text
boxes
in
the footer aligned under the columns I need to sum. In the
control
source
for each of these boxes I have typed =Sum([Amount Payment Type
1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the
numbers
are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead.
What
did
I
do wrong?

 

[Duane Hookom]

To get the Total Payment, you in your report, all you should need to do is
add a text box with a control source like:
=Nz([Field1],0) + Nz([Field2],0)
The Grand totals in a Footer section should be:
=Sum([Field1])
=Sum([Field2])
=Nz(Sum([Field1]),0) +Nz(Sum([Field2]),0)
Replace my example field names with your actual field names. These
expressions should work in group or report headers or footer (not page
headers and footers).
--
Duane Hookom
MS Access MVP

I truly appreciate your assistance with the problem I am having, but I feel
a
little softer approach would go a long way. With the exception of actual
records, I thought I had given you what you asked for.

contact amount amount
name payment payment Total
type 1 type 2 Payment

Duane $1000.00 $2000.00 $3000.00
Teri $1000.00 $1000.00 $2000.00

Grand Totals $2000.00 $3000.00 $5000.00

With the exception of capitalization in the column titles (and our names),
the report should look just like the above. Is there a way I can attach
the
report so you can actually see it?

Why don't you simply reply to my request "Can you provide some sample
records and desired display in the report?"
You provided some column names/titles and a description but not what I
had
expected.
--
Duane Hookom
MS Access MVP

What I have is something like the following:

Contact Name Payment Type 1 Payment Type
2

What the boss wants done is to total each column (ie - all of Payment
Type
1) and have the total appear below the appropriate column (you already
helped
me with this and I am all set with it). Then he wants to total for
each
Contact Name the Payment Type 1 and Payment Type 2 amounts and have
that
total appear to the right of the Payment Type 2 column for each Contact
Name
given.

I have experimented and tried putting the same type of unbound box that
totals each column in the Details section of the report and what it
does
is
give me the total for the two column totals as opposed to each row on
the
report.

Please let me know if I am unclear in any way and I will try to
clarify.

:

"calculate rows with the total to the right" could mean a lot of
different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather
than
summing a single field across records, you should explain why you have
your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you
tell
me,
is it possible to calculate rows with the total to the right? Or
can I
do
it
right in the table so that I can include it in a report?

:

I think you want to use the Report Footer. To create Group Footers,
you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should
go.
How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and
Group
Footers.
I expect you might have chosen the Page footer which will not
work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at
the
bottom
of
for two of the columns. What I have done is put two unbound
text
boxes
in
the footer aligned under the columns I need to sum. In the
control
source
for each of these boxes I have typed =Sum([Amount Payment Type
1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the
numbers
are
being
pulled from have the same exact name). When I view the report
in
layout
view
I do not see the total of the column above, but #Error
instead.
What
did
I
do wrong?

 

[Guest]

Never mind, I figured it out. I created a query with the formula I needed
and used that in my report. But thank you so much for the help you gave me
with the first portion of my problem!!

Many Thanks,

Teri.

I truly appreciate your assistance with the problem I am having, but I feel a
little softer approach would go a long way. With the exception of actual
records, I thought I had given you what you asked for.

contact amount amount
name payment payment Total
type 1 type 2 Payment

Duane $1000.00 $2000.00 $3000.00
Teri $1000.00 $1000.00 $2000.00

Grand Totals $2000.00 $3000.00 $5000.00

With the exception of capitalization in the column titles (and our names),
the report should look just like the above. Is there a way I can attach the
report so you can actually see it?

Why don't you simply reply to my request "Can you provide some sample
records and desired display in the report?"
You provided some column names/titles and a description but not what I had
expected.
--
Duane Hookom
MS Access MVP


What I have is something like the following:

Contact Name Payment Type 1 Payment Type 2

What the boss wants done is to total each column (ie - all of Payment Type
1) and have the total appear below the appropriate column (you already
helped
me with this and I am all set with it). Then he wants to total for each
Contact Name the Payment Type 1 and Payment Type 2 amounts and have that
total appear to the right of the Payment Type 2 column for each Contact
Name
given.

I have experimented and tried putting the same type of unbound box that
totals each column in the Details section of the report and what it does
is
give me the total for the two column totals as opposed to each row on the
report.

Please let me know if I am unclear in any way and I will try to clarify.

:

"calculate rows with the total to the right" could mean a lot of
different
things that would require me to guess. I prefer to not guess. Can you
provide some sample records and desired display in the report?

If you have a need to sum fields/columns across a single record rather
than
summing a single field across records, you should explain why you have
your
table structured in a way that requires this.
--
Duane Hookom
MS Access MVP

Okay, got that going right finally, thank you so much! Now, can you
tell
me,
is it possible to calculate rows with the total to the right? Or can I
do
it
right in the table so that I can include it in a report?

:

I think you want to use the Report Footer. To create Group Footers,
you
must
use the Sorting and Grouping dialog.
--
Duane Hookom
MS Access MVP

Yes, it is the page footer. That is where I thought it should go.
How
do
I
find the group footers?

:

What do you mean by "the footer"? There are Report, Page, and Group
Footers.
I expect you might have chosen the Page footer which will not work.
--
Duane Hookom
MS Access MVP

I have created a report that I need to put the grand total at the
bottom
of
for two of the columns. What I have done is put two unbound text
boxes
in
the footer aligned under the columns I need to sum. In the
control
source
for each of these boxes I have typed =Sum([Amount Payment Type
1])
in
one
and
=Sum([Amount Payment Type 2]) in the other (the fields the
numbers
are
being
pulled from have the same exact name). When I view the report in
layout
view
I do not see the total of the column above, but #Error instead.
What
did
I
do wrong?

 

[Kae]

Hello,


My question is about Sum Column report using MS Access 2003.

I have design table, query's and now I have design the report based on the
information that I have in a Query.


The format of the column where I want to get a total value is an Long
Interger (General Number) type. However, I can see that when I apply the
txtbox in the Detail section and Run Sum to All elements in the column and I
add the txt box which will display the results in the Page Footer, it won't
display the total.
I'm not sure if this is due to a format, perhaps if works for Currency in
another report that I designed it shouldn't represent an issue when you are
trying to add just numbers.

Here is the example:
Totalhours {Field Name}
1.4
1.58333333333333
2.08333333333333
3.25
3.91666666666667
0.833333333333333
1.4
2.75
0.683333333333332
3


All this should have a total of 20.9 reflected in the Page Footer, but when
I follow the steps in the doc that you referred in this post I only get
returned the value in the last row.

I even try to trick these by poring the total sum in a query, perhaps I
can't call that field (from that query) in the report, when the actually
report is calling for a different table.

If you have any suggestions I really appreciate any input that you can
provide...

Thanks a lot,

Ps: Did you know how I can reduce the format or length in the numbers to 6
digits, I try to reformatted in the table perhaps I'm obtained that value
trough a SQL Sentenced.
here is just in case you need to look at it, so you can understand what I'm
talking about.

SELECT tbl_main.TimeBegin, tbl_main.TimeEnd,
Format((([TimeBegin]-[TimeEnd])*1440*-1)/60) AS Totalhours
FROM tbl_main;

This is how I get the numbers that I want to ADD as Total in the bottom in
the report. And I think that's why I can't change the length but I'm not sure
if there is a way around this.

Thanks again in advance,
Sincerely
Kae

 

[Marli]

Hi There
I need help on how to make a run sum on a ascending, like a bank balance,
always having the newest data on the top and balance too.

Thanks

Marli