Problem formatting report with multi-column sub-report

Dale Fye
Dale Fye used Ask the Experts™
on
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.multi-column right-then-down sub-reportAny 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).
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
In a subreport you can only use Across then Down.

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.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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).
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
< 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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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?
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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_Product_History report by itself, it format exactly the way I want the final version formatted, but without the header data.

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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Let's see what Pete says, I wont be able to look at this in depth until tomorrow evening...
MIS Liason
Most Valuable Expert 2012
Commented:
Dale,

You know I couldn't sleep with this hanging over me...
;-)

How about Just using the subreport as itself, as the Main report (Make sense?)
(Since the subreport (opened alone) will format properly with all the columns)

Group it by PropNum
Create a Group Footer and set the Page break for "After Section"
Then throw PropNum in the Page header and use Dlookups (in the Page header) to pull in all of the "Main" fields...

(I am sure you see where I am going with this...)
I created another Main record and a few sub records to verify the page break, and get a better view of the overall report

Let me know...

;-)

Jeff
SampleReport.mdb
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<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
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
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...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial