Need help creating/generating 5-page report

[shanice]

Hello All,

I want to create a 5-page report in Access. My first question is there anyway to do this in one report? I tried and it won't go any longer than 22". I have 37 cities that I am collecting information on but I want to generate a report (including each of the 5 pages) for each city, is there an easy way to do this?

Any help will be greatly appreciated! Thanks!!!

 

[wiklendt]

yes, there is an easy way to do it in 'one' report.

any other questions?

 

[shanice]

Yes, just one question. How? I've tried using page breaks but it still cuts off 22.

 

[wiklendt]

i have generated reports that are hundreds of pages long. the first thing you need is to base your report of a query, which is made from good, normalised tables. the second things you MAY need, is to use SUBreports.

that is, make a query based on your cities. then make a report based off that query.

then, to make a subreport, make a query based on the details you want your cities to have (it is easy to determine if you need/want a subreport - if your data is split into a 1-many table structure, then the "main" report is based on the data from "1" and the subreport is the "many").

so, if i have a table called tblPeople, then i have another table called tblTraining, i can make a report based off a query that pulls all the People data i want, and then i can make another report based off the tblTraining data, then i link the two on my primary/foreign key IDs and make the training report a SUBreport of the People report. (edit: as in, i physically use the "subreport" control from the toolbar to plonk the training report onto the people report - and follow the prompts re: linking. the training report is a regular report until it is on another report, where it then becomes known as a 'sub' report - like your training table is just a regular table - then you relate it to the people table with a foreign key, it becomes a "many" table to your people table (which is the '1' table). get it?)

(edit: so "1" person can have "many" training sessions - a 1-many relationship, often annotated on these fora as "1-m")

so person 1 may have completed 10 training exercises, but person 2 might only have completed 2, etc. all this data will display in a report for as many pages as the data goes. (edit: but person 2 will not have extra space under their name if you select the subreport control properties "can grow/shrink" to 'yes')

why, how are you doing it?

 

[shanice]

Thanks for your response! I created a report for each of the 5 pages. Then I pulled all 5 reports (as subforms) into a blank report. All the relationships are set up so it appears to be functioning properly, BUT when I go to report view the report cuts off at the top of the 3 page...I'm assuming because I reached the maximum height (22").

Am I doing something wrong?

 

[boblarson]

Yes, you are doing something wrong. It is a bit hard to describe and might be easier if you could upload your database for us to take a look.

 

[wiklendt]

can't really determine that from your description. my gut feeling tells me you probably haven't normalised your data properly.

are you able to post a (sanitised) version of your database here? (with a brief description of which report it is that you're having trouble with - some people post their database asking about a combobox, and then you open their database and it has 27 forms in it....!)

(edit: bob is correct, you ARE doing something wrong, but what we can't tell from your description is HOW you are doing it wrong...)

 

[shanice]

It's connected to SharePoint so I don't think you'll be able to open it. I'll create a fake database and load it up.

 

[shanice]

Okay this isn't the best example, but it does show what I'm trying to do. If you see I have 3 pages of different information and 26 different grantees/states that I want to generate this information for (pretending it was pulling actual data). How do I get it to show the 3 page combined for each state at a time...like Alaska...page 1...page 2...page 3 then Alabama page 1...page 2...page 3. Not Alaska page1...Alabama page 1.

Any help is greatly appreciated!!! The subforms are in "combined reports."

 

[DCrake]

There are several issues with your combined report

1. You need to insert a group header and footer into the report (ID)
2. Make both header and footer invisible
3. set the master and child fields to ID for all sub reports
4. Reduce the size of the sub reports to about 2 inch high and set the Can Grow and Can shrink to Yes
5. Remove the page header/ footer sections from the report.
6. Insert page break controls between each sub report.
7. Change the paper style to A4 from Letter

Save and display the report in print preview.

Make the necessary mods to suit.

 

[shanice]

Thank you so much! It worked!

1. I'm exporting the report to Word, do you know how I can get the footer to show up correctly? Right now it's all of over the place.

2. Isn't there a way that I can type in the state name and have only that report pop-up? It seems like I've done this before, but I can't remember off the top of my head.