Report Footer Grand TL not appearing on one page report with colum

[Chiz]

In Access 2007, I have a report with 2 columns of data with sums created in a
footer. At the bottom of the one-page report, I would like the Grand Total. I
can calculate the Grand Total in the Report Footer, but it jumps to a second
page even when I have plenty of room on the first page. I do not have this
problem when page setup has data reading across the 2 columns, but I would
like the data to read vertically and not horizontally. Thank you.

 

[Marshall Barton]

In Access 2007, I have a report with 2 columns of data with sums created in a
footer. At the bottom of the one-page report, I would like the Grand Total. I
can calculate the Grand Total in the Report Footer, but it jumps to a second
page even when I have plenty of room on the first page. I do not have this
problem when page setup has data reading across the 2 columns, but I would
like the data to read vertically and not horizontally.

You can get the grand totals to appear in a column (after
the group footer) by inserting a top level group using a
constant expression such as =1. Then a text box in this
group footer can use expressions like =Sum(amount) just
link in your existing group footer.

 

[Chiz]

Thanks for the response, Marsh, but either I'm not understanding or maybe I
didn't explain clearly enough what I'd like to do. I'd like to see the Grand
Totals at the bottom of my report. The numbers in my example (the 20 for
apples, etc.) are already in a footer since they are each a sum. If I have a
2 page report, my Grand Totals show up nicely using the Report Footer, but
this isn't working on a one-page report. (??) Would be grateful for another
answer...

INVENTORY
Produce Dairy
Apples 20 Milk 10
Pears 10 Yogurt 13
Bananas 5 Cheese 5
TOTAL 35 28

 

[Marshall Barton]

I think I see now. AFAIK, there is no way to get the report
footer on the first page when you use down then across
columns. The first column is completely filled before the
second column starts so there is no room at the bottom of
the page for the report footer.

One approach with down then across is to use your report as
a subreport and set the subreport control's Can Grow
property to No. That will not accomodate a situation where
you have more data records than will fit in the subreport's
fixed space.

A different, but much more complex, approach is to use the
report's record source query to calculate a field that when
sorted will make an across then down report look like its
down then across. For a 2 column report that is guaranteed
to fit on one page, the calculated field would be something
like:

Col: (2 * (SELECT Count(*) FROM table As X WHERE
table.sortfield < X.sortfield)) \ (SELECT Count(*) FROM
table)

Then the report can group on the Col field and use
NewRowOrCol property to get to the second column.

If the report's data might not fit on one page, then you can
use a one column subreport bound to the record source query,
modified as above. Add two text boxes: txtCol1 and txtCol2
with control source expressions =0 and =1. Place two copies
of the subreport side by side and set the LinkMaster
property tp txtCol1 or txtCol2 with the LinkChild field set
to the Col field. Set CanGrow to Yes for both copies of the
subreport.

It would be a heck of a lot easier to just use across then
down and avoid all that.

 

[Chiz]

Thanks so much for the detailed answer! I'll be trying your suggestions ASAP.

I think I see now. AFAIK, there is no way to get the report
footer on the first page when you use down then across
columns. The first column is completely filled before the
second column starts so there is no room at the bottom of
the page for the report footer.

One approach with down then across is to use your report as
a subreport and set the subreport control's Can Grow
property to No. That will not accomodate a situation where
you have more data records than will fit in the subreport's
fixed space.

A different, but much more complex, approach is to use the
report's record source query to calculate a field that when
sorted will make an across then down report look like its
down then across. For a 2 column report that is guaranteed
to fit on one page, the calculated field would be something
like:

Col: (2 * (SELECT Count(*) FROM table As X WHERE
table.sortfield < X.sortfield)) \ (SELECT Count(*) FROM
table)

Then the report can group on the Col field and use
NewRowOrCol property to get to the second column.

If the report's data might not fit on one page, then you can
use a one column subreport bound to the record source query,
modified as above. Add two text boxes: txtCol1 and txtCol2
with control source expressions =0 and =1. Place two copies
of the subreport side by side and set the LinkMaster
property tp txtCol1 or txtCol2 with the LinkChild field set
to the Col field. Set CanGrow to Yes for both copies of the
subreport.

It would be a heck of a lot easier to just use across then
down and avoid all that.
--
Marsh
MVP [MS Access]

Thanks for the response, Marsh, but either I'm not understanding or maybe I
didn't explain clearly enough what I'd like to do. I'd like to see the Grand
Totals at the bottom of my report. The numbers in my example (the 20 for
apples, etc.) are already in a footer since they are each a sum. If I have a
2 page report, my Grand Totals show up nicely using the Report Footer, but
this isn't working on a one-page report. (??) Would be grateful for another
answer...
INVENTORY
Produce Dairy
Apples 20 Milk 10
Pears 10 Yogurt 13
Bananas 5 Cheese 5
TOTAL 35 28

.