 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2006multi page report - which is best method ??
I have a 12 page report that I need to print, I was thinking that there would be a few ways to go about this, one would be to have a different grouping for each page, the other would be to somehow link one report to the next, and another method I thought of was to create 12 single page reports and run them off a macro where I open one report, then the next, then the next...etc... There are parts of each page that use option groups to show if the person filling out the form selected yes, no or n/a and there are other parts of the report that build statements thruough queries that are based on questions on the form. I would like to be able to run this off a command button, but it is essential that the 12 reports be linked together under one common document number... An added bonus would be to have the end of one page, if it is less than one full page be picked up by the start of the next page, in other words, not necessarily ending up with 12 sheets of paper for each report. I have a form that works fine to gather the information into various tables, it is just finding a way to get all the fields out of the tables and into reports that is proving to be difficult. M G Henry >I have a 12 page report that I need to print, I was thinking that >there would be a few ways to go about this, one would be to have a [quoted text clipped - 20 lines] >tables, it is just finding a way to get all the fields out of the >tables and into reports that is proving to be difficult. I strongly suggest you use a single report. The secret to creating a report is to organize the needed data using a query. Once you have a query that selects the desired data, the report should be pretty easy to create by specify groups and using various property settings (e.g. KeepTogether, ForceNewPage, etc). If it is too difficult to gather the data using a single query (pretty unlikely), then create subreports for each independent part of the data. If you need more specific assistance, please post details of about the tables and their fields along with how they should appear in the report.  SignatureMarsh MVP [MS Access] > >There are parts of each page that use option groups to show if the > >person filling out the form selected yes, no or n/a and there are other > >parts of the report that build statements thruough queries that are > >based on questions on the form. > I strongly suggest you use a single report. > [quoted text clipped - 15 lines] > Marsh > MVP [MS Access] Marsh, This project originated from having a word document that has 12 different pages in it, there is a header page, that I have built into a header table ... that table has a primary key that is an autonumber field called statement number that I am using to keep the other tables linked together... Each of the other 11 tables (pages) have a top section where there are up to 10-15 statements that have an option group as these statements have a possible answer of yes, no or n/a, and the report that they want to have generate will also use an option group with these same 3 options to show which of the 3 was chosen when the document was filled out. in each table (page) under this section, there is another series of questions that are yes/no type fields and some of these questions have text that link into them. There are quite a few quesitons that are for example... does the customer require purge processing yes no. if yes is chosen then a statement is build that says [customer name] (from the header page) will require purge processing. if the answer "no" is slected then nothing should appear on the report. An example of the yes/no with text would be...the question "are there variable images involved in this project" yes no and under that quesion would be the next statement "describe the variable images" ( followed by the text field) [varimg] what this needs to generate in the report is a statement either for a yes answer being ... " There will be variable images involved in this project" follwed by another statment ... the variable images will be [varimg] if the answer to the question is no, then there should be nothing on the report whatsoever. I have built queries that use IIF commands to check the values of the various yes no options and build the appropriate statements using concantenations or simple statements. the form section of this database works fine and loads the tables appropriately and when I view the queries, the IIF commands build the statements correctly, it is just a matter of laying down everything after the header page to both flow correctly and hopefully result in a report that will not have huge gaps of white space in it where there are answers of "no" to the questions contained in the form. The way that this 12 page form and subsequent report exists is that there are probably about 300 questions that are made up thought the entire project. I hope this gives you some more insight, and I greatly appreciate your quick response. >> >There are parts of each page that use option groups to show if the >> >person filling out the form selected yes, no or n/a and there are other [quoted text clipped - 61 lines] >there are probably about 300 questions that are made up thought the >entire project. The only question I see in there is how to avoid blank space for questions that don't appear. Presumably your query is supplying Null or "" (ZLS - zero length string) values for the suppressed questions. In this case and as long as the report has no visible controls in the same horizontal "band", then you can set the question text box's and its section's CanShrink property to Yes. If there are some other (non text box) controls that need to be made invisible (e.g. options), then use some VBA code in the section's Format event: Me.othercontrol.Visible = (Nz(Me.questionx, "") = "") SignatureMarsh MVP [MS Access] > >> >There are parts of each page that use option groups to show if the > >> >person filling out the form selected yes, no or n/a and there are other [quoted text clipped - 78 lines] > Marsh > MVP [MS Access Marsh, The thing is that I don't have very much experience in building forms from scratch, and I had read that I will run into a 22" length limit... I started to build the report in fact and was told that the length of the form exceeded the maximum length... so I am not sure that I am building it correctly... There was also a width issue but that was easily fixed, it is getting all 12 pages of potentially 11" report onto one report that is also proving to be frustrating... should I be using groupings ?? and if so I have 12 pags and am only allowed 10 groups, or is there a way around that ?? Sorry for my lack of understanding, but I have relied too heavily on wizzards to get my reports created and I do not have a good understanding of how the report structure can be built from scratch... M G Henry >> >> >There are parts of each page that use option groups to show if the >> >> >person filling out the form selected yes, no or n/a and there are other [quoted text clipped - 92 lines] >wizzards to get my reports created and I do not have a good >understanding of how the report structure can be built from scratch... I presume that you using the word "form" when you mean to use "report". I am really having trouble picturing what you are trying accomplish and what data tables and fields you want in the report. Almost all reports have a record source query and the report displays each record's data by using bound controls in the detail section (which is commonly less than a half an inch high). I do not understand how your report's data and/or layout differs from the norm. Exceeding 22" for a section of a report is extrordinarily unusual. Except for an odd kind of unbound report, I don't think I have ever seen one of these monsters. Go back to my earlier thoughts about creating a query to organize the data or use a subreport for each independent set of data and see if you can relate to any of that or explain how and why your situation is different. SignatureMarsh MVP [MS Access] > >> >> >There are parts of each page that use option groups to show if the > >> >> >person filling out the form selected yes, no or n/a and there are other [quoted text clipped - 115 lines] > Marsh > MVP [MS Access] Marsh, I apologize for not making myself clearer, I have only been writing to groups for a few weeks and this is a tough report design situation... ( or maybe I am just making it tough )... This project comes out of a 300 + question questionairre that was originally done using Microsoft Word. The part of the original pages that used option groups was well suited to Word, because it was just a check box on a word form. The part that did not suit Word well was the part where the questions were just yes/no ( approx 60% of the questions. ) The reason was, that if the answer to the question was yes, the final word document had to be rewritten ( for example we work with variable images as part of the every day work that we do... so the quesion would be asked.... "does this project involve variable images ? " if the answer to this question was no, reference to it would always be deleted in the final document. If the answer was yes, below it would be a question describe the variable images... so when the person would work with this document in word, he/she would have to rewrite the question as a statement... ie: This project involves variable images. The variable images are a corporate logo, a corporate image...... ) The reason why I see that this is more suited to an Access application is because the way that the yes/no questions were (when answerd as yes ) and turned into statments was always a similar pattern, one that could be written into IIF statements very effectively. The reporting issue comes in that there are 300+ different questions, which in a word document takes up 12 different pages of data to type out. When I go to manually layout a page in a report, ( something I have very little experience in ) I have a few limitations that I am running into. I cannot physically layout 300+ questions in 11" .... I am not sure if I should use groupings to break this down, but if I do, I still can only put down 10 groups ( no way to fit 12 pages ) I know that I can use subreports and that they can be based on forms, just not sure if that is the direction I should be going in... or if I should be building smaller reports and basing the subreports on these reports.... The other thing is that there are parts of each of the 12 pages that use option groups, they are to be reported just like they appear in the forms, word for word, character for character... With the yes/no quesitons, if the answer is no, nothing should appear ( this I am covering in the building of the query with null fields being assigned to no answers. if the answer is yes, I can build real nice statements using IIF command and concantenations.) It all looks good, what I have built so far in the tables and the queries. The problem lies in putting down the non option group statements and making the report appear to flow, without gaps... That is my goal, so if each of the 12 pages is only 50% filled out with yes responses, say, I would like to end up with a 6+ page report and not a 12 page report every time. 75% filled out yes, then 8pg+ report, etc.... I am sorry that I cannot make myself clearer in fewer words, I greatly appreciate your time and consideration.... M G Henry >I apologize for not making myself clearer, I have only been writing to >groups for a few weeks and this is a tough report design situation... ( [quoted text clipped - 54 lines] > I would like to end up with a 6+ page report and not a 12 page report >every time. 75% filled out yes, then 8pg+ report, etc.... I think I understand, at least a little, how you want the finished report to appear. What I have no clue about is the **structure** of the data. I need to know the tables, the fields in each table and any queries that are involved in getting the desired data into the report. I guess your original question kind of implies that you have one monster table with lots and lots of fields (255 is the upper limit). If that's the case, all of your difficulties stem from that poor table structure. Most of what I've said earlier was based on a "Normalized" (see http://support.microsoft.com/kb/283878/en-us) table structure involving a few "related" tables with only a few fields/columns in each table. The question table would have a lot of records with one row/record for each question. With this kind of structure, you would not have the problem I think you are trying to describe. SignatureMarsh MVP [MS Access] > >I apologize for not making myself clearer, I have only been writing to > >groups for a few weeks and this is a tough report design situation... ( [quoted text clipped - 76 lines] > Marsh > MVP [MS Access] I want to thank you so much for your help thus far... I will try and be consice in this answer... The report is based on 12 different tables one for each page... the first page of the report ( page 1 ) holds a document number field that is a primary key ( to set up the relationships to the other tables) and a field for customer name, document name and date, that is all that is in that table. Each of the other 11 tables have from 1 to 15 number fields that the option group from the yes/no/n/a option group is bound, and then however many fields are necessary for that particular page to cover all the yes/no quesitions, ( some of these yes/no fields have immediately next to them the related text fields, that are used to build the IIF statements in the queries. ) The queries call on the common docuement number field, and perhaps the customer name or document name field, plus the associated fields from the associated tables to build the queries upon which the reports are based. ( eg... for page 2, the query takes from page 1 and 2.... for page 3, page 1 and 3, etc... ) and the IIF statments build fields that are either simple statements or concantenations with text fiels, so I have 11 queries that make up those 11 pages of reprorts. >From what I am understanding, perhaps, I should be building subforms for each of the 11 pages that handle the yes/no situations ( taking the fields built by the IIF commands and laying them one under the other ) and then somehow I would have other subforms that would handle the option groups ? and then combining them onto a master report ?? The thing is, how do I get around the 22" limitation once I string all this stuff into one long report ? 12 pages of 11" of report comes out to 132 liner inches of report ?? this is where I am confused. Thanks so much Marsh for your patience with me. M G Henry > > >I apologize for not making myself clearer, I have only been writing to > > >groups for a few weeks and this is a tough report design situation... ( [quoted text clipped - 76 lines] > > Marsh > > MVP [MS Access] I want to thank you so much for your help thus far... I will try and be consice in this answer... The report is based on 12 different tables one for each page... the first page of the report ( page 1 ) holds a document number field that is a primary key ( to set up the relationships to the other tables) and a field for customer name, document name and date, that is all that is in that table. Each of the other 11 tables have from 1 to 15 number fields that the option group from the yes/no/n/a option group is bound, and then however many fields are necessary for that particular page to cover all the yes/no quesitions, ( some of these yes/no fields have immediately next to them the related text fields, that are used to build the IIF statements in the queries. ) The queries call on the common docuement number field, and perhaps the customer name or document name field, plus the associated fields from the associated tables to build the queries upon which the reports are based. ( eg... for page 2, the query takes from page 1 and 2.... for page 3, page 1 and 3, etc... ) and the IIF statments build fields that are either simple statements or concantenations with text fiels, so I have 11 queries that make up those 11 pages of reprorts. >From what I am understanding, perhaps, I should be building subforms for each of the 11 pages that handle the yes/no situations ( taking the fields built by the IIF commands and laying them one under the other ) and then somehow I would have other subforms that would handle the option groups ? and then combining them onto a master report ?? The thing is, how do I get around the 22" limitation once I string all this stuff into one long report ? 12 pages of 11" of report comes out to 132 liner inches of report ?? this is where I am confused. Where I was running into 255+ fields was when I was trying to put all the fields into the report record source by dragging down the * to bring all the fileds into the query, but there are in fact 12 separate tables. Thanks so much Marsh for your patience with me. M G Henry > > > >I apologize for not making myself clearer, I have only been writing to > > > >groups for a few weeks and this is a tough report design situation... ( [quoted text clipped - 76 lines] > > > Marsh > > > MVP [MS Access] Marsh, I want to thank you so much for your help thus far... I will try and be consice in this answer... The report is based on 12 different tables one for each page... the first page of the report ( page 1 ) holds a document number field that is a primary key ( to set up the relationships to the other tables) and a field for customer name, document name and date, that is all that is in that table. Each of the other 11 tables have from 1 to 15 number fields that the option group from the yes/no/n/a option group is bound, and then however many fields are necessary for that particular page to cover all the yes/no quesitions, ( some of these yes/no fields have immediately next to them the related text fields, that are used to build the IIF statements in the queries. ) The queries call on the common docuement number field, and perhaps the customer name or document name field, plus the associated fields from the associated tables to build the queries upon which the reports are based. ( eg... for page 2, the query takes from page 1 and 2.... for page 3, page 1 and 3, etc... ) and the IIF statments build fields that are either simple statements or concantenations with text fiels, so I have 11 queries that make up those 11 pages of reprorts. >From what I am understanding, perhaps, I should be building subforms for each of the 11 pages that handle the yes/no situations ( taking the fields built by the IIF commands and laying them one under the other ) and then somehow I would have other subforms that would handle the option groups ? and then combining them onto a master report ?? The thing is, how do I get around the 22" limitation once I string all this stuff into one long report ? 12 pages of 11" of report comes out to 132 liner inches of report ?? this is where I am confused. Where I was running into 255+ fields was when I was trying to put all the fields into the report record source by dragging down the * to bring all the fileds into the query, but there are in fact 12 separate tables. Thanks so much Marsh for your patience with me. M G Henry >> >I apologize for not making myself clearer, I have only been writing to >> >groups for a few weeks and this is a tough report design situation... ( [quoted text clipped - 106 lines] >this stuff into one long report ? 12 pages of 11" of report comes out >to 132 liner inches of report ?? this is where I am confused. The 22" limit may be where you think you are confused, but as I said before, the real confusion is in your table structure. Having a separate table for each page is not a good table structure and will introduce all kinds of troubles in such things as your forms and reports. Ignoring all that, I think(?) you can get the effect you want if you create a separate report for pages 2-12. You can then place those subreports in a main report that has the page 1 information in its report header section. The main report's detail section would then only contain the 11 subreports. Make each subreport control fairly short (only an inch or so) and set their CanGrow property to Yes. Also set the main report's detail section's CanGrow property to Yes. SignatureMarsh MVP [MS Access] |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.