Problem formatting report with multi-column sub-report
I'm trying to get this report to work the way I would like, but am having difficulty.
In the main form I'm displaying data that pertains to an individual oil/gas well. This info is all displayed in the [PROPNUM] group header. In the detail section of that report, I have dropped a subreport that works great (down-then-right) with headers on each column (when displayed on its own). However, as soon as I drop this report into the main report and link it on [PROPNUM] I get a single column report. If I change the multi-column format to right-then-down, I get the attached image.
Any ideas how to resolve this? I've thought about putting three instances of the sub-form and limiting there result sets so that I have rows in each column, but that seems like a lot of work (although I'd be done now if I did that from the start).
In the main form I'm displaying data that pertains to an individual oil/gas well. This info is all displayed in the [PROPNUM] group header. In the detail section of that report, I have dropped a subreport that works great (down-then-right) with headers on each column (when displayed on its own). However, as soon as I drop this report into the main report and link it on [PROPNUM] I get a single column report. If I change the multi-column format to right-then-down, I get the attached image.
Any ideas how to resolve this? I've thought about putting three instances of the sub-form and limiting there result sets so that I have rows in each column, but that seems like a lot of work (although I'd be done now if I did that from the start).
ASKER
Peter,
That is what I found on another site, but I was hoping someone knew of a creative way around that. I was thinking about a way to sort the records so that they actually print out right-then-down but would appear in the sequential order and appear as though they were printed down-then-right. But I still cannot figure out how to get the column headings over all three columns (when printing right-then-down).
--------------------------
I would disagree with the statement:
"..the subreport has no idea about page size, which is controlled by the main report .."
since on the main report, I have set the detail sections height to a specific height, and set the CanGrow property to No. I would think the subreport would capture that information from the main report before it starts to format.
--------------------------
If no one else has any suggestions, what I think I will try is to generate a temporary table which ranks the order of the records based on the P_Date field. Then I'll put three separate versions of the subform side-by-side in the main forms Detail section, each with its own recordsource that includes a specified number of records (1-to-n, n+1-to-2n, 2n+1-to-3n).
That is what I found on another site, but I was hoping someone knew of a creative way around that. I was thinking about a way to sort the records so that they actually print out right-then-down but would appear in the sequential order and appear as though they were printed down-then-right. But I still cannot figure out how to get the column headings over all three columns (when printing right-then-down).
--------------------------
I would disagree with the statement:
"..the subreport has no idea about page size, which is controlled by the main report .."
since on the main report, I have set the detail sections height to a specific height, and set the CanGrow property to No. I would think the subreport would capture that information from the main report before it starts to format.
--------------------------
If no one else has any suggestions, what I think I will try is to generate a temporary table which ranks the order of the records based on the P_Date field. Then I'll put three separate versions of the subform side-by-side in the main forms Detail section, each with its own recordsource that includes a specified number of records (1-to-n, n+1-to-2n, 2n+1-to-3n).
< If I change the multi-column format to right-then-down, I get the attached image.>
<Any ideas how to resolve this? I>
Not being familiar with your data or the design of the report, can you tell us what is wrong with the report in the image...?
<I would disagree with the statement:
"..the subreport has no idea about page size, which is controlled by the main report ..">
Actually Pete is correct, you can confine/restrict the subreport in any manner of ways, but the "Page" (the actual Page that will be printed out), is always controlled by the main report.
The setting you mentioned have nothing to do with the "Page" settings/Options.
The settings you mentioned are on the Main report, which again, controls the subreport.
So the subreport only knows about "page" settings based on the Main report.
<If no one else has any suggestions, what I think I will try is to generate a temporary table which ranks the order of the records based on the P_Date field. Then I'll put three separate versions of the subform side-by-side in the main forms Detail section, each with its own recordsource that includes a specified number of records (1-to-n, n+1-to-2n, 2n+1-to-3n).>
:-O
Seems like an extremely radical approach...
<If no one else has any suggestions, >
...In a sample DB:
Post a sample of your raw data.
Post a clear, graphical representation of the *exact* output you desire (based on the sample data)
JeffCoachman
<Any ideas how to resolve this? I>
Not being familiar with your data or the design of the report, can you tell us what is wrong with the report in the image...?
<I would disagree with the statement:
"..the subreport has no idea about page size, which is controlled by the main report ..">
Actually Pete is correct, you can confine/restrict the subreport in any manner of ways, but the "Page" (the actual Page that will be printed out), is always controlled by the main report.
The setting you mentioned have nothing to do with the "Page" settings/Options.
The settings you mentioned are on the Main report, which again, controls the subreport.
So the subreport only knows about "page" settings based on the Main report.
<If no one else has any suggestions, what I think I will try is to generate a temporary table which ranks the order of the records based on the P_Date field. Then I'll put three separate versions of the subform side-by-side in the main forms Detail section, each with its own recordsource that includes a specified number of records (1-to-n, n+1-to-2n, 2n+1-to-3n).>
:-O
Seems like an extremely radical approach...
<If no one else has any suggestions, >
...In a sample DB:
Post a sample of your raw data.
Post a clear, graphical representation of the *exact* output you desire (based on the sample data)
JeffCoachman
ASKER
Jeff,
I knew you would eventually chime in. By the time I ran into this problem last night it was already 10:30, so I decided to call it a night, and posted the note here this AM.
I'll create a sample database and post it when I get home this evening.
I was being facetious (or at least trying to be) in the "I disagree..." segment. I was hoping that by clearly defining the limits of the Detail section (CanGrow = No), that Access would be able to format the subform down-then-right within the detail section of the main form.
Maybe I don't need the subform at all, and can achieve the page width header info and still get the multiple columns?
I knew you would eventually chime in. By the time I ran into this problem last night it was already 10:30, so I decided to call it a night, and posted the note here this AM.
I'll create a sample database and post it when I get home this evening.
I was being facetious (or at least trying to be) in the "I disagree..." segment. I was hoping that by clearly defining the limits of the Detail section (CanGrow = No), that Access would be able to format the subform down-then-right within the detail section of the main form.
Maybe I don't need the subform at all, and can achieve the page width header info and still get the multiple columns?
LOL
Hey Dale...
ooops...
I didn't realize that was you...
(you know I never look at usernames or Points values...)
...just being my usual "gruff", (but respectful) self...
;-)
I kinda knew what you meant, by your "disagree" point, ...but again, ...I did not know I was in the presence of greatness...
;-)
...so my comment of:
"Seems like an extremely radical approach..."
...Did not take into account the level of User I was up against.
;-)
So yes, "you' can use multiple sub reports
;-)
As you know, reports are kinda "My Thing"...
...so I am always interested in seeing if something unique is possible...
Note that Pete and Jim actually got me "hot" for reports, so they may have something up their sleeves as well...
;-)
Jeff
Hey Dale...
ooops...
I didn't realize that was you...
(you know I never look at usernames or Points values...)
...just being my usual "gruff", (but respectful) self...
;-)
I kinda knew what you meant, by your "disagree" point, ...but again, ...I did not know I was in the presence of greatness...
;-)
...so my comment of:
"Seems like an extremely radical approach..."
...Did not take into account the level of User I was up against.
;-)
So yes, "you' can use multiple sub reports
;-)
As you know, reports are kinda "My Thing"...
...so I am always interested in seeing if something unique is possible...
Note that Pete and Jim actually got me "hot" for reports, so they may have something up their sleeves as well...
;-)
Jeff
ASKER
OK, here is a sample database with two tables, two queries and the report/subreport.
My goal is for every page to contain the information that is in the dark blue header, and then for each property, to have three columns of data (no more than one page per Property) with the most recent data in the left most column, and column headers for each set of columns.
If you open the qry_rpt_Nope_Properties_Pr
If you open the main report, without reformatting the sub-report, it will only print one column of data. But if you change the column layout to right-then-down, you get what I posted in my original post.
Would appreciate any ideas (and I'm not bound to the report/sub-report construct if you can figure something else out).
SampleReport.mdb
My goal is for every page to contain the information that is in the dark blue header, and then for each property, to have three columns of data (no more than one page per Property) with the most recent data in the left most column, and column headers for each set of columns.
If you open the qry_rpt_Nope_Properties_Pr
If you open the main report, without reformatting the sub-report, it will only print one column of data. But if you change the column layout to right-then-down, you get what I posted in my original post.
Would appreciate any ideas (and I'm not bound to the report/sub-report construct if you can figure something else out).
SampleReport.mdb
Let's see what Pete says, I wont be able to look at this in depth until tomorrow evening...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Jeff,
Sorry to keep you from sleep.
I could see where the DLOOKUPs would take for ever for the 2700 Property version of this report, but for opening the report using the Where Argument of the OpenReport method, that should work great.
Much simpler than the method I proposed yesterday. I'm glad that I didn't get a chance to work on that last night.
Sorry to keep you from sleep.
I could see where the DLOOKUPs would take for ever for the 2700 Property version of this report, but for opening the report using the Where Argument of the OpenReport method, that should work great.
Much simpler than the method I proposed yesterday. I'm glad that I didn't get a chance to work on that last night.
<I could see where the DLOOKUPs would take for ever for the 2700 Property version of this report, but for opening the report using the Where Argument of the OpenReport method, that should work great. >
"Just Crazy enough to work" eh?
LOL!
Yeah, I knew you would see where I was going with my approach...
I'm sure we could have possibly made a monster query and based the report on that...
But I was trying to keep the design as close to what you posted...
As always, ...Glad to help a fellow Expert
;-)
Jeff
"Just Crazy enough to work" eh?
LOL!
Yeah, I knew you would see where I was going with my approach...
I'm sure we could have possibly made a monster query and based the report on that...
But I was trying to keep the design as close to what you posted...
As always, ...Glad to help a fellow Expert
;-)
Jeff
But actually, this was the first time I was exposed to this issue
(I never inserted a Column Report as a sub report)
So this one goes in my archives...
(I never inserted a Column Report as a sub report)
So this one goes in my archives...
You cannot use Down then Across because the subreport has no idea about page size, which is controlled by the main report; so the subreport never gets to the end of a page.