469,934 Members | 2,421 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

Report on 2 Columns and Report Total

97 64KB
Hello friend

I made a report on 2 columns. It is OK... Question is:

if the report ends at Left side column, the report Total prints in the same side.

but if the report ends in the Right side column, report goes on to the next page and print the Total on Left side. Means one more page consume....

Kindly guide me how to solve this problem. Thanks
irsmalik
Dec 30 '20 #1

✓ answered by twinnyfo

I do believe this will always be the case, because the report footer is not created by Access until all the records hav been displayed. If the first column spills to the second column, there is no longer room at the bottom of the page for the Report Footer. It MUST go to a new page.

28 2978
isladogs
317 Expert Mod 256MB
Put the total in the report footer or possibly a group footer section. If necessary adjust the height of the footer section accordingly
Dec 30 '20 #2
irsmalik
97 64KB
dear Mr isladogs

Report Total is already in the Report's footer. (in the left side of report's footer)

irsmalik
Dec 30 '20 #3
isladogs
317 Expert Mod 256MB
Then try adjusting the footer height as suggested...or use a group footer
Dec 30 '20 #4
twinnyfo
3,653 Expert Mod 2GB
irsmalik,

There may also be some setting in your sections which are forcing a new page after the section. Check on that as well.
Dec 30 '20 #5
isladogs
317 Expert Mod 256MB
Hi twinnyfo
I thought that was unlikely as when only one column is populated the total is on the same page. Perhaps I'm mistaken?
Dec 30 '20 #6
twinnyfo
3,653 Expert Mod 2GB
IslaDogs,

I think that is "usually" the case. My experience with reports is that different combinations of things sometimes give different results. I just want to make sure OP is exploring all options.

It could be that if the records are completed in the first Column (by a few records), that the record footer can fit. But, when breaking to the second column, the first column goes to the absolute bottom limit of the column and does not begin to format the report footer (because the data has not completed). The report footer will only go onto a page if there is enough room at the bottom of the page for the entire footer. When the column breaks to the second column, obviously, there are records there. I don't believe Access Reports are "smart" enough to make such adjustments. (One might could fix this programmatically with VBA is the detail OnFormat--but that's another story altogether, and one I am not prepared to address at this moment.)

It would be interesting to note if the Report footer would force a next page if there were exactly enough records to complete one column, but not push to the second column?

I think you are correct though, as no page is being forced with smaller data sets.
Dec 30 '20 #7
SwissProgrammer
213 128KB
twinnyfo,

Could it be that the Report footer has a new-line at the end of it's text and maybe that is making the Report footer in combination with the records beyond the allowable height of the page? Maybe make the Report footer shorter in height? Maybe make the records top and/or bottom white-space shorter in height?
Dec 30 '20 #8
NeoPa
32,232 Expert Mod 16PB
@Irsmalik.
Perhaps it would be a good idea at this stage to include all those Report Properties that you understand may affect this issue.

If there are more that you aren't aware of then I expect this will be brougght up later but that information is almost certainly necessary to understand your situation properly.

The info we have so far is only part of what the question should be.
Dec 30 '20 #9
irsmalik
97 64KB
Dear All Friends

Thanks for your response.

May I could not explain my question properly. Please see the attached Report...

This report ends at page 3 at Right side (2nd column). Its Grand Total should be print after the Data finish.

But the Grand Total moves to 4th page. This is my question. The Grand total must be print after the data finish.. either it is it Column 1 (left side) or Column 2 (right side.).

Margins are set as per the required space from top and bottom.
Thanks
irsmalik
Attached Files
File Type: pdf Farmers Meeting.pdf (119.4 KB, 27 views)
Dec 31 '20 #10
NeoPa
32,232 Expert Mod 16PB
Irsmalik. I'm going to assume that your difficulty understanding is due to the language barrier. Please re-read my last post. It is very important that you give us the properties of the Report that you've designed otherwise we're all floundering (bumping around) in the dark. It's very wasteful to have so many people work without providing them the basic information they need.
Dec 31 '20 #11
irsmalik
97 64KB
Mr NeoPa
Language problem still exist and it will be in future.

Report Properties in jpg format is attached.
Page Setup in jpg format is attached.

thanks
irsmalik


Attached Images
File Type: jpg properties.jpg (125.5 KB, 180 views)
File Type: jpg page.jpg (86.3 KB, 176 views)
Dec 31 '20 #12
isladogs
317 Expert Mod 256MB
@irmsalik
Check the highlighted properties of the report footer section of your report. Yours may be different. For example, you may have Force New Page = Before section

Try altering the settings ...one at a time ...till you get what you want.

Attached Images
File Type: png Capture.PNG (11.0 KB, 185 views)
Dec 31 '20 #13
irsmalik
97 64KB
Dear Mr isladogs..

I have checked all options one by one..... but result is same... if Report ends in 2nd columns... Grand Total move on to Next Page.

Thanks for your help.
irsmalik
Dec 31 '20 #14
twinnyfo
3,653 Expert Mod 2GB
I do believe this will always be the case, because the report footer is not created by Access until all the records hav been displayed. If the first column spills to the second column, there is no longer room at the bottom of the page for the Report Footer. It MUST go to a new page.
Dec 31 '20 #15
NeoPa
32,232 Expert Mod 16PB
Irsmalik:
Language problem still exist and it will be in future.
Very true. We make allowances for this.
Irsmalik:
Report Properties in jpg format is attached.
Please understand that attached pictures will not be accessible by some users. Where possible, and I've just updated yours to show how, please show the picture as part of the post at least.

Actually, I wasn't asking for all properties, but I do appreciate that you won't always understand what I say clearly so what you've done is plenty good enough ;-)
TwinnyFo:
It MUST go to a new page.
Quite right. Consider how Access handles the first column. It will only start on the second one when the first is full. In this case Full means when it has used up all the available vertical space in the Detail Section. This is determined by the paper height, minus vertical space used before the section, also minus the page footer.

Thus there can never be vertical space, in this scenario, for a Report Footer.

As that is the very point of the answer to the question I've gone ahead and set that as Best Answer.
Dec 31 '20 #16
isladogs
317 Expert Mod 256MB
Hi
Whilst the logic of twinnyfo's post makes total sense, I knew it was possible to do this...so I created a test database to illustrate how its done. Whether it will be suitable for the OP is another matter.

When you split report data into 2 columns, there are two methods of sorting:
1. Down then across (default)
2. Across then down

Using method 1, twinnyfo's comments are 100% true. What the OP wants isn't possible

However using method 2, it does work ...but for exactly the same reasons as it didn't work in method 1. The report footer can be displayed on the same page as the detail section isn't full.

The attached report has 2 columns and 3 pages. The report footer is shown on the final page of the data (page 3).
NOTE: The data is for a fictitious school.



P.S. I've finally worked out how to do inline images here. Unless I'm being dumb, it's not that obvious but it does work!

P.P.S. Happy New Year to all. Lets hope its a good one... without any fear (John Lennon)
Attached Images
File Type: png Capture.PNG (36.3 KB, 174 views)
Attached Files
File Type: zip 2ColumnReportTest.zip (122.0 KB, 15 views)
Dec 31 '20 #17
NeoPa
32,232 Expert Mod 16PB
Nice one IslaDogs. Very interesting and helpful.

I'm not sure it's exactly what the OP asked for, but let's see what they make of it. It's good to know anyway but if they decide it works for them I'll change the Best Answer for the thread.

Frankly, in a situation like this, it would be useful to set both as BA as they both include very pertinent information, but alas only one is allowed.

It's great when people think outside the box and provide answers that are valid (In this case possibly so.) yet without contradicting one saying it can't be done.

PS. Every user has the rights to edit their own posts so if ever you see one of yours with something in it you don't know about you can select Edit and review exactly what is used to get that effect. Another useful resource can be found at BB Code.
Jan 1 '21 #18
irsmalik
97 64KB
Dear Mr Isladogs

Thanks for your time and effort. I really appreciate it.

1. Down then across (default)
2. Across then down

When we use method 2, all the section wise data (I mean Area wise data & its Total) are not inline and scattered. but grand total prints after data is finished.

Method 1 is good, but after data is finished, the grand total moves to next page.
so this is the situation. I prefer method 1 as it describe all the data in proper way with Area and its total. Method 2 is not working as per my requirement.

But I really thank you for you time & effort.
irsmalik
Jan 1 '21 #19
isladogs
317 Expert Mod 256MB
No problem. I didn't expect it would suit your needs. However I think we all now understand the reasons why each method works as it does.

Thanks to both twinnyfo for the explanation and to NeoPa for your comments
Jan 1 '21 #20
chrstnslvdr
3 2Bits
Try changing the footer height
Jan 27 '21 #21
NeoPa
32,232 Expert Mod 16PB
Hi Christian.

If you read the discussion that's already been had then you'll understand that won't help.
Jan 27 '21 #22
isladogs
317 Expert Mod 256MB
Unless I'm mistaken, I've just realised that all of us missed something obvious....

As already explained by @twinnyfo, a 2 column report arranged down then across will ALWAYS place the report footer on a new page where the report data ends in the right hand column

As I explained above, a workround is to arrange the 2 column report across then down ...but this layout is often unacceptable to users

However there is another solution .... using a subreport!

Attached is a modified version of the example app I uploaded in post #17.
It contains the following 2 column reports with data occupying both columns of 3 pages:
a) rptY7PupilData-AcrossThenDown - report footer is on page 3
b) rptY7PupilData-DownThenAcross - report footer is on its own on page 4
c) rptSubY7PupilData-DownThenAcross - identical to b) so report footer is on page 4 .... if opened as a standalone report
BUT now the 'clever bit' ....
d) rptY7PupilDataMain ...an empty container with subreport from c) - report footer is on right hand column of page 3

OK...so who feels confident to explain the result of d)? There is absolutely no trickery involved
Attached Files
File Type: zip 2ColumnReportTest_v2.zip (127.9 KB, 15 views)
Jan 27 '21 #23
twinnyfo
3,653 Expert Mod 2GB
Wow, Dog! I think you hit on something here! I "think" I can explain it that you have the subreport shrunk to half-width with the CanGrow property set to True. It will grow by itself in one column, and then move to the next column. At the end, when it runs out of records, the subreport is still only "half width", but on the next column. Thus it adds the subreport's footer underneath itself.

This is a guess, but it sounds neat!

That's some good police work!
Jan 27 '21 #24
isladogs
317 Expert Mod 256MB
Hi @twinnyfo
Even if you increase the subreport width to say 19cm, the footer will still be on the right column of page 3

EDIT
In my example, the subreport was 2 columns and the detail section of the main report was also 2 columns
For info, you can change the subreport to single column and say 9cm wide.
However, when used as a subreport, the footer remains on page 3

So...does that strengthen the evidence for your suggested explanation or otherwise?
Jan 27 '21 #25
twinnyfo
3,653 Expert Mod 2GB
I don't know.....

I think Access has a mind of its own.

Either way, your solution absolutely works! It is a bit roundabout, but not unorthodox. Very well done!
Jan 27 '21 #26
isladogs
317 Expert Mod 256MB
OK, here's my attempt at explanation. Possibly not that different to yours

It appears that the main form is entirely responsible for controlling the layout. The subform layout is ignored.

As confirmation that the subform column property is utterly irrelevant, keep the subform as two columns and full width.
Make the main form single column so it has enough room to display both columns of the subform.
However, that doesn't happen.
The subform is displayed with the right hand column completely blank and the report will now take up 6 pages

As long as the main form has two columns, it doesn't matter whether the subreport is defined as one column or two
The subreport is treated as a single column and displayed twice side by side.

Hence, the subreport footer will always appear immediately below the final record (in the right or left hand column as appropriate)

I presume Access ignores the subreport layout to prevent any potential issues of conflicting logic
Jan 28 '21 #27
twinnyfo
3,653 Expert Mod 2GB
That makes total sense.

Do you think MS has documented that phenomenon anywhere? (wink-wink)
Jan 28 '21 #28
isladogs
317 Expert Mod 256MB
No idea about it being documented as I haven't checked....but I somehow doubt it!

EDIT:
I've done a quick search and not found anything so far.
I did however find this MSDN thread which gives incorrect advice: Subreport with columns

I have just found this article which describes the original problem posed here by the OP and a different more complicated solution:
Reports-multiple-columns-and-subreports/
Jan 28 '21 #29

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Themis Parintas ste kardias | last post: by
1 post views Thread by larry mckay | last post: by
2 posts views Thread by bobw2961 | last post: by
1 post views Thread by bobh | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.