Calculating the Grand Total in the Report

  • Thread starter Thread starter lil lam
  • Start date Start date
L

lil lam

Hi,

I have a report. In the report, there is a field Current Market Value
(CMV), and I am trying to create a grand total for it at the header or
footer of the report.

I tried what was in the book:
-create a text box, and now i see 2 boxes with the following text in
it: Text#: and Unbound
-go to properties
-go to data tab
-enter formula in control source ---> =sum([field])
-change running sum to overall or group over

This hasnt been successful for me.
Can someone help?
It should be real simple, i dont know why its not working.
Can someone help?
 
Make sure your text box is in the Report Header or Footer and not a Page
Header or Footer. The Running Sum should NOT be set to overall or over group.
Leave it as None.

This all assumes your field is a numeric field in your report's record source.
 
Make sure your text box is in the Report Header or Footer and not a Page
Header or Footer. The Running Sum should NOT be set to overall or over group.
Leave it as None.

This all assumes your field is a numeric field in your report's record source.

--
Duane Hookom
Microsoft Access MVP

lil lam said:
Hi,
Click to expand...
I have a report. In the report, there is a field Current Market Value
(CMV), and I am trying to create a grand total for it at the header or
footer of the report.
Click to expand...
I tried what was in the book:
-create a text box, and now i see 2 boxes with the following text in
it: Text#: and Unbound
-go to properties
-go to data tab
-enter formula in control source ---> =sum([field])
-change running sum to overall or group over
Click to expand...
This hasnt been successful for me.
Can someone help?
It should be real simple, i dont know why its not working.
Can someone help?
Click to expand...
Click to expand...

You're a genuis!!!
Thank you!!!
So simple yet I was stumped for hours !!!
 
Really, thanks so much!!
I can move on with my day now !!!
You dont know how appreciative I am !!!! :)
 
Hey,

I'm having a problem with totals. I created a report that lists each
companies totals each month. At the bottom of the report, I have included a
Sum. This will sum up each of the column (month) totals. I'd also like to
include a grand total column (1 for each company over 12 months), as well as
a grand total for all companies. I was able to get a total by entering:

=Sum([April_Weight]+[May_Weight]+[June_Weight]+[July_Weight]+[August_Weight]+[September_Weight]+[October_Weight]+[November_Weight]+[December_Weight])

However, for upcoming months, there is not currently any data in that field.
I'd like to ignore these null values to get my totals. The only way it
will allow me to do this is by entering a 0 in each of the months (fields)
that have not occured yet. Is there another way to do this?

Thank you.

Sum

Duane Hookom said:
Make sure your text box is in the Report Header or Footer and not a Page
Header or Footer. The Running Sum should NOT be set to overall or over group.
Leave it as None.

This all assumes your field is a numeric field in your report's record source.

--
Duane Hookom
Microsoft Access MVP


lil lam said:
Hi,

I have a report. In the report, there is a field Current Market Value
(CMV), and I am trying to create a grand total for it at the header or
footer of the report.

I tried what was in the book:
-create a text box, and now i see 2 boxes with the following text in
it: Text#: and Unbound
-go to properties
-go to data tab
-enter formula in control source ---> =sum([field])
-change running sum to overall or group over

This hasnt been successful for me.
Can someone help?
It should be real simple, i dont know why its not working.
Can someone help?
Click to expand...
Click to expand...
 
You can use the Nz() function to convert Nulls to 0. For example:
=Sum(...+Nz([November_Weight],0)+Nz([December_Weight],0))
This assumes the fields are available in the report's record source.

Are you really storing values in fields with the Month name as part of the
field name? Isn't this a bit un-normalized?

--
Duane Hookom
Microsoft Access MVP


Dabeaks said:
Hey,

I'm having a problem with totals. I created a report that lists each
companies totals each month. At the bottom of the report, I have included a
Sum. This will sum up each of the column (month) totals. I'd also like to
include a grand total column (1 for each company over 12 months), as well as
a grand total for all companies. I was able to get a total by entering:

=Sum([April_Weight]+[May_Weight]+[June_Weight]+[July_Weight]+[August_Weight]+[September_Weight]+[October_Weight]+[November_Weight]+[December_Weight])

However, for upcoming months, there is not currently any data in that field.
I'd like to ignore these null values to get my totals. The only way it
will allow me to do this is by entering a 0 in each of the months (fields)
that have not occured yet. Is there another way to do this?

Thank you.

Sum

Duane Hookom said:
Make sure your text box is in the Report Header or Footer and not a Page
Header or Footer. The Running Sum should NOT be set to overall or over group.
Leave it as None.

This all assumes your field is a numeric field in your report's record source.

--
Duane Hookom
Microsoft Access MVP


lil lam said:
Hi,

I have a report. In the report, there is a field Current Market Value
(CMV), and I am trying to create a grand total for it at the header or
footer of the report.

I tried what was in the book:
-create a text box, and now i see 2 boxes with the following text in
it: Text#: and Unbound
-go to properties
-go to data tab
-enter formula in control source ---> =sum([field])
-change running sum to overall or group over

This hasnt been successful for me.
Can someone help?
It should be real simple, i dont know why its not working.
Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
That worked perfectly. I'm still learning all the ins and outs of access.
So far, this has worked.

I have one additional question, though. That worked for the grand total.
Is there a way to total each row? The main layout would be

Company Jan Feb Mar April May etc... (Total Column is what
I want)

XYZ Co. 15 200 25 60 20 ... (Need this #)
ABC Co. 25 140 0 28 15 .... (Need this Number)

Total: 40 340 25 88 35 .... Grand Total
(works, thank you.)

ll=> Just used a text box for this

Duane Hookom said:
You can use the Nz() function to convert Nulls to 0. For example:
=Sum(...+Nz([November_Weight],0)+Nz([December_Weight],0))
This assumes the fields are available in the report's record source.

Are you really storing values in fields with the Month name as part of the
field name? Isn't this a bit un-normalized?

--
Duane Hookom
Microsoft Access MVP


Dabeaks said:
Hey,

I'm having a problem with totals. I created a report that lists each
companies totals each month. At the bottom of the report, I have included a
Sum. This will sum up each of the column (month) totals. I'd also like to
include a grand total column (1 for each company over 12 months), as well as
a grand total for all companies. I was able to get a total by entering:

=Sum([April_Weight]+[May_Weight]+[June_Weight]+[July_Weight]+[August_Weight]+[September_Weight]+[October_Weight]+[November_Weight]+[December_Weight])

However, for upcoming months, there is not currently any data in that field.
I'd like to ignore these null values to get my totals. The only way it
will allow me to do this is by entering a 0 in each of the months (fields)
that have not occured yet. Is there another way to do this?

Thank you.

Sum

Duane Hookom said:
Make sure your text box is in the Report Header or Footer and not a Page
Header or Footer. The Running Sum should NOT be set to overall or over group.
Leave it as None.

This all assumes your field is a numeric field in your report's record source.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I have a report. In the report, there is a field Current Market Value
(CMV), and I am trying to create a grand total for it at the header or
footer of the report.

I tried what was in the book:
-create a text box, and now i see 2 boxes with the following text in
it: Text#: and Unbound
-go to properties
-go to data tab
-enter formula in control source ---> =sum([field])
-change running sum to overall or group over

This hasnt been successful for me.
Can someone help?
It should be real simple, i dont know why its not working.
Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top