Calculated control: Grand Total

  • Thread starter Thread starter Aria
  • Start date Start date
A

Aria

This seems like such an easy thing to do, and I feel kind of dumb asking, but
I haven't been able to figure it out. Being a beginner, I could use a little
help, please. I am working on a report that list department information for
our school. I finally have everything looking the way I would like. I have a
dept. name header that list each dept. and a dept. name footer that sums the
dept. total.
Paper usage is an issue so I am trying to cut down on the amount of paper
that will be needed to print the report. The information displays in 3
columns across the page. In the report footer, I have a calculated control,
grand total. The only problem I am trying to resolve is how to get the grand
total to appear at the end of the last dept. info. What's happening is the
3rd column on the last page with dept. info does not extend to the bottom of
the page. I thought the grand total would appear on this page since there is
plenty of room. It doesn't. It appears alone on the next page. The only other
item on the page is the page header. Is there any way to correct this? I've
checked the Help section in Access and previous posts. I seem to be doing it
correctly but I was just wondering if there's a way.
 
Hi Aria,

The most likely cause of this is that the Force New Page setting for the
Report Footer (in the Format tab of the property sheet for the Report Footer
section) is not set to "None".

Another possibility is that you've got some white space below the control(s)
in the Report Footer, and have Keep Together set to Yes in the Sorting and
Grouping dialog; with insufficient space for the whole Report Footer, this
would also force a new page before it.

HTH,

Rob
 
Hi Rob,
Thanks for responding. You're correct. The Force New Page is set to "None".
What should it be set to? I've tried each of the available options without
any change in the outcome.
Again, you are correct in that there was some white space below the control,
which I have changed. I have the DeptName page header and footer set to "Keep
Together". Should this be changed? The report footer "Keep Together" is set
to "No". The outcome, so far, has remained the same. Have I missed something
that you wanted me to try?
 
Hi Aria,

I suspect that, since neither of these things fixed it, that it's because I
missed an important point in what you wrote: "... the 3rd column on the last
page with dept. info does not extend to the bottom of the page". It seems
that you're wanting (expecting) the report footer to fit into the column;
sadly, that will not happen: Report Header/Footer sections (and Page
Header/Footer sections) will always occupy the entire page width. You may
be able to get around this by setting the Columns to "Across, then down", to
force the columns to display more evenly spaced down the page, which may
leave enough room for the Report Footer.

Again, HTH,

Rob
 
Hi Rob,
Well, you're right again. I did want and expect that the total could fit
into the space that wasn't used by the last column (more than half the page
downward isn't used). I tried changing the margins and column spacing. I also
tried your last suggestion (across then down) but it then only allowed one
dept. per page, which more than tripled the amount of paper that would be
needed. What a disappointment. That grand total on the final page is going to
bug the heck out of me but what can you do? We tried.
Thank you so much for your help and suggestions.
 
Aria said:
Well, you're right again. I did want and expect that the total could fit
into the space that wasn't used by the last column (more than half the page
downward isn't used). I tried changing the margins and column spacing. I also
tried your last suggestion (across then down) but it then only allowed one
dept. per page, which more than tripled the amount of paper that would be
needed. What a disappointment. That grand total on the final page is going to
bug the heck out of me but what can you do?
Click to expand...


Because the report footer spans the entire width of the
report, it's not really appropriate for what you want. You
probably need to use a group footer, which will be in a
single column.

To do that, insert a new group level at the top of the
sorting and grouping list using a constant expression (e.g.
=1). Then put the grand total text box in this new group
footer section.
 
Marsh,
....and there you have it. I'm all smiles now. Perfect! Although, if you
wouldn't mind one last question. I don't understand the purpose of "=1"? Is
that the column?
 
Aria said:
...and there you have it. I'm all smiles now. Perfect! Although, if you
wouldn't mind one last question. I don't understand the purpose of "=1"? Is
that the column?
Click to expand...


If you look at the heading over the first column in the
sorting and grouping window, you will see that it is
'"Field/Expression". When you use an expression, it must
start with an = sign. In this case, the expression is a
trivial constant such as =1, but it could be anything that
is not the name of a field. E.g. you could use:
="My Dummy Footer"
to the same effect).

This may be more than you want to know, but there are, of
course, many situations where it is very useful to use a
more complex expression such as:
=[last name] & " " & [first name]
but there are also several situations where ot is useful to
group on a constant as discussed above or even group on the
same field multiple times.
 
If you look at the heading over the first column in the
sorting and grouping window, you will see that it is
'"Field/Expression".
Click to expand...

I had to take another look at that as I never thought about what is written
there. I only thought about fields in the sorting & grouping box. Funny how
you can look at something over and over and never really see it.
...or even group on the same field multiple times.
Click to expand...
With help from someone here, I just recently learned how to do this.

Thank you so much, Marsh, for helping me with this and for teaching "why"
and "how". Thanks also to you Rob for giving me suggestions on where to look
and possible problems to look for. I've learned so much from those who are
more than willing to teach.
--
Aria W.


Marshall Barton said:
Aria said:
...and there you have it. I'm all smiles now. Perfect! Although, if you
wouldn't mind one last question. I don't understand the purpose of "=1"? Is
that the column?
Click to expand...


If you look at the heading over the first column in the
sorting and grouping window, you will see that it is
'"Field/Expression". When you use an expression, it must
start with an = sign. In this case, the expression is a
trivial constant such as =1, but it could be anything that
is not the name of a field. E.g. you could use:
="My Dummy Footer"
to the same effect).

This may be more than you want to know, but there are, of
course, many situations where it is very useful to use a
more complex expression such as:
=[last name] & " " & [first name]
but there are also several situations where ot is useful to
group on a constant as discussed above or even group on the
same field multiple times.
Click to expand...
 
Back
Top