• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2456
  • Last Modified:

How to layout a multi-page report

I want to create a report that will contain a dozen subreports.

The 1st page of the report is filled up with information from the primary table for primary key 'ABC'.  

Each subreport will contain data from a separate table for foreign-key 'ABC'

Sometimes, NONE of the other tables will have any data related to 'ABC'

My challenges:
There is no room in the Detail Section to drag a subreport without increasing the height size of the Detail Section.  If I increase the height size of the detail section, then multiple pages will print for record 'ABC' regardless of whether the Subreports have anything to print.

I tried turning on 'Can Shrink' and 'Auto Height' for the main report Detail Section hoping either of these might solve the problem... but no joy.

How can I easily eliminate this challenge?

With Regards-
Sam
0
SAbboushi
Asked:
SAbboushi
  • 6
  • 6
  • 3
2 Solutions
 
GRayLCommented:
If the report format of each of the sub reports is identical, you could use code to 'test' each table for data, and if none, skip to the next source object.  So, in summary, one main report, one sub report, and code in detail that does the test for data and assigns the source object of the sub report accordingly.
0
 
GRayLCommented:
Actually, the format of each sub report need not be identical.  It would just be simpler if it were so.  If some or all were different, it would be a matter for the code to test each table for a recordset on the given criteria, pick the right sub report for the table, and make the source object assignment.
0
 
Jeffrey CoachmanMIS LiasonCommented:
SAbboushi,

My first question would be:

What are you trying to display with this report?

12 subreports, seems to be pushing the limits of what can be displayed and still be understandable.

Are you sure this is the best design?
Why not relate the tables and create a "Grouped" Report?
In a grouped report, if the group has no records, it will not print.

JeffCoachman
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SAbboushiAuthor Commented:
Thanks for your posts.

OK - I figured out the issues:
1) Can Shrink ONLY applies to the vertical space taken up by CONTROLS.  Any blank (vertical) space between controls will NOT shrink.  A subreport is a control.  I need to make sure that any vertical space of my Detail section that is beyond the page boundary needs to contain a control(s) in it in order for the Detail section to shrink should the controll be Null.
2) The control must ALSO be set to "Can Shrink" = yes.

Jeff:
>> Why not relate the tables and create a "Grouped" Report?
What is that and how do I do it?

>> In a grouped report, if the group has no records, it will not print.
Not sure what this means.  There will ALWAYS be records in Table1 (the primary Table).  The rest of the tables may have some records, or not.  Are you using the word 'group' to reper to each table (i.e. a group is a table) and that there is the ability to create a 'grouped report' where all the groups are related, and only those groups that have data will print?  Yes - as long as these groups are selecting records where the foreign key matches the primary key in Table1.

0
 
GRayLCommented:
Sorry, guys, I guess I was having a pipe dream.  I don't thing one can loop in a report like I envisioned.  You could do it in the code behind a button on a form where you could call a form and set the control object on the fly following a test for data.  
0
 
Jeffrey CoachmanMIS LiasonCommented:
SAbboushi,

"Jeff:
>> Why not relate the tables and create a "Grouped" Report?
What is that and how do I do it?"

Here is a sample, notice that Customer: "Sally" ... has no orders and they do not show up on the report.
(As you know in a Subreport setup, "All" main records display.)

You can create it easliy if your tables are properly related in the database window.

Just start the form Wizard and first select the Parent Table record, then select the Child Table records.
Then group by the Customer.
The wizard will walk you through all the steps.

;-)

JeffCoachman
db173.mdb
0
 
SAbboushiAuthor Commented:
Thanks Jeff - that helped give me an intro to the basics of the form & reporrt wizeards... but, seems the grouping is heirarchical (i.e. group Table3 data within Table2 data within Table1 data).  I need Table2 and Table3 data to be at the same level.  

Let's say I have another table with address info in it (yes - this is a bogus example, but I'm trying to understand the building blocks here - I don't care what letters or words are on them!)

Can I create the following report using the wizard or in some other automated / near automated manner?

CustID: 119      Cust Name: Bob                          <---- Primary Table

        OrderID     OrderDate   etc...                       <---- Table1 related data
        1001          1/1/2009
        1018          2/14/2009

        Address        City              State    etc...      <---- Table2 related data
        123 main        San Diego    CA

CustID  178
etc...

I already solved the part about the extra white space printing - so right now, I'm just focusing upon the easiest way to create that report which has a dozen tables of related info that I need to print
0
 
Jeffrey CoachmanMIS LiasonCommented:
SAbboushi,

Again like in your previous post, Can you explain what the actual tables are?

For example, you say:
  " I need Table2 and Table3 data to be at the same level. "
Three tables, got it, ...Great.

Then you post:
CustID: 119      Cust Name: Bob                          <---- Primary Table

        OrderID     OrderDate   etc...                       <---- Table1 related data
        1001          1/1/2009
        1018          2/14/2009

        Address        City              State    etc...      <---- Table2 related data
        123 main        San Diego    CA
...Where there is no Table 3?
Is the Primary table Table 1?

Table 2 is showing what exactly?

I really don't want to start woking on this full steam, only to have you delete it suddenly.

JeffCoachman
0
 
SAbboushiAuthor Commented:

Hi Jeff:
I appreciate all the help and knowledge you have given me over the past week.  Your posts have generally been of a more well-thought-out and relevant nature than the average.

First, my feedback:
>> I really don't want to start woking on this full steam, only to have you delete it suddenly.

I don't think your comment is fair.  In the other post, not a single response addressed my very specific question.  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_24147807.html

I figured out the solution on my own and posted it.  Sadly, it appears you were unaware that I had 'closed' the question because of an objection that was placed (and over-ruled).  Although you posted something you thought would be helpful, and I am sorry you spent the time doing so (being unaware that I had 'closed' the question), your post did not address my very specific question.

Bottom line: characterizing my behavior as one who 'deletes questions suddenly' seem to me neither fair nor accurate.  And... if I figure out the solution myself after determining that several responses have not been helpful, I am not sure whether you are suggesting it inapporpriate that I post my own solution and close the question.

Second:  For future reference, I have been working with computer technology since 1971, so I've been around the block a few times.  Most of the time, my posts are oversimplified examples so that I can gain the knowledge I seek.  If you wish to offer alternatives, please feel free to do so (I've appreciated them)... but please be forwarned that I most likely am still looking for an answer to my original question.

Now regarding my example (if you still care to help...):
I was sloppy... let me paste and then correct the Table #s below in BOLD):

... seems the grouping is heirarchical (i.e. group Table3 data within Table2 data within Table1 data).  I need Table2 and Table3 data to be at the same level.  
Let's say I have another table with address info in it (yes - this is a bogus example, but I'm trying to understand the building blocks here - I don't care what letters or words are on them!)

Can I create the following report using the wizard or in some other automated / near automated manner?

CustID: 119      Cust Name: Bob                          <---- Primary Table [TABLE1]

        OrderID     OrderDate   etc...                       <---- Table1 related data [TABLE2]
       1001          1/1/2009
        1018          2/14/2009

        Address        City              State    etc...      <---- Table2 related data [TABLE3]
        123 main        San Diego    CA

CustID  178
etc...

0
 
Jeffrey CoachmanMIS LiasonCommented:
Ummm.

OK, lets start over then.
;-)

My post was not meant to be taken offensively.

As Mbizups post stated in your last question, she was pretty confident that you had a firm grasp on concept of the Linking fields.
This turned out to be true as you ultimately arrived at the solution yourself.

My comment was meant to say that If you were almost finished with this, I did not want to confuse matters by suggesting a different approach.

So my post was more of an alert to you that I might make a few suggestions that you might not want to persue.

This is one of the problems with not seeing a person face to face.

Sometimes the written word does not capture facial expressions, vocal inflections, body language, ...ect

Suffice it to say that I would not have created my post, if I was going to start out by being offensive.

Are we cool?
;-)

So let's get the Party started!

First question:
Is it possible for you to post a sample of this databse?
This is because in your above scenario you have:
One customer (Table1)
Many (2) Orders (Table2)
One address (table 3)
I need to know how that one address relates to the two orders and the One customer.

Jeff
0
 
SAbboushiAuthor Commented:
>> Are we cool?
;-)

Thanks Jeff - you have certainly passed the 'Cool' test by my standards.   Peace!

>>Is it possible for you to post a sample of this databse?
Samples attached.  After uploading, realized you miay not have 2007 so uploaded an .mdb version too.

Report1 is what I ended up creating manually.  As you can see, there is no resemblance to my example.
I was trying to figure out how to use the wizards and other automation tools to create as much of this report automatically as possible, and then allow me to go in and tweak the rest manually.  If you can do something like this with grouped reports, please let me know!  Any other thoughts / suggestions on what you see would also be appreciated.

And thanks for the nice and peaceful response.
With Regards-
Sam
Jeff.txt
Jeff.mdb
0
 
SAbboushiAuthor Commented:
Print Preview Report1
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, I will take a look at this tonight
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK,

To be perfectly honest, I tried, but I could not really understand the layout of the report.

If your main concern is having a Sub report display even when there is no data for it, then again, I suggest you create a grouped Report, using the wizard.
See if this gives you what you are looking for first.

Then perhaps you can add these "unrelated" subreports later.

JeffCoachman
0
 
SAbboushiAuthor Commented:
K - thanks for the effort
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now