How to have a report with more than 2 pages? (1 Viewer)

[waleedimtiaz]

Hey everyone, I am a beginner in MS Access. I want to make a report with around 4-5 pages, but Access limits me to make a report within 2 pages only.
I tried to make two different reports and then tried to combine them as subreports, in the REPORT VIEW I can see all 4 pages, but in the PRINT PREVIEW I can't see all the pages, in fact, I can only see a blank page as there is no space to combine these 2 reports in the print preview.

 

[plog]

Debugging 101 - Get something working.

Sounds like you tried to do everything at once and there's so many things its impossible to tell which one went sideways. I would start a new report and try and build the report you want one piece at a time. Add 1 field and test it to see that it does what you expect. Once it does, add the second piece, test to see if it works like you want, then add a third, test it.....etc etc.

If you would like to post your database I'm sure we could diagnose the issue. From experience Access definitely doesn't limit you to just 2 pages. And I mean that in a couple of ways--1 if you have a million names in a table and just have it spit out name after name after name it will create as many pages as necessary to spit out those million names. 2- If I want to manually force a page break after every single one of those names such that my report is 1 million pages long it will allow me to do that as well.

 

[Pat Hartman]

If you need four pages to lay out all your data fields, your table is not correctly defined so you need to take a pretty big step backwards. It's time to do some reading about normalization and go back to the drawing board to recreate your tables. If you post the db, we can take a look at it.

 

[waleedimtiaz]

Debugging 101 - Get something working.

Sounds like you tried to do everything at once and there's so many things its impossible to tell which one went sideways. I would start a new report and try and build the report you want one piece at a time. Add 1 field and test it to see that it does what you expect. Once it does, add the second piece, test to see if it works like you want, then add a third, test it.....etc etc.

If you would like to post your database I'm sure we could diagnose the issue. From experience Access definitely doesn't limit you to just 2 pages. And I mean that in a couple of ways--1 if you have a million names in a table and just have it spit out name after name after name it will create as many pages as necessary to spit out those million names. 2- If I want to manually force a page break after every single one of those names such that my report is 1 million pages long it will allow me to do that as well.

Actually, the 4 pages I've mentioned is for the SINGLE record.

 

[waleedimtiaz]

If you need four pages to lay out all your data fields, your table is not correctly defined so you need to take a pretty big step backwards. It's time to do some reading about normalization and go back to the drawing board to recreate your tables. If you post the db, we can take a look at it.

The problem is that I am making a report of 4 pages for a SINGLE record.

 

[plog]

Zip and post a copy of your database.

 

[The_Doc_Man]

OK, there is a limit here that you DO need to know about. Everything in report layout is based on sections, and sections have a limit of 22.75 inches high and wide. For standard 8.5x11 paper, if you have 1-inch margins all around, you have (8.5 inches - 2 x 1 inch margins=) 6.5 usable inches... x 4 = 26 inches. You cannot make a 4-page-wide report on portrait mode that uses the entire 4th page because the section can't be that wide. Section size is a hard limit that you will have a devil of a time in breaking. Note also that if you have things in landscape mode, that's (11 inches - 2 x 1 inch margins =) 9 usable inches and there, x 3 pages is 27 inches - again, over the section limit.

The basis for this limit is that Access uses a unit called twips for placement of objects. The unit is such that 1440 twips = 1 inch in either direction - horizontal or vertical. The slot that holds the width or length of a section and the slots that hold horizontal and vertical positions are limited to 16 bits AND cannot be negative, so the effective limit is 0 to 32767 as the largest positive 16-bit integer. If you do the math, 22.75*1440 = 32760, which is why you cannot get a section to go any wider or longer. If your design absolutely requires 4 sheets across (or 3 in landscape), you have a major problem using Access. Not impossible, as you COULD try to make two or three parallel reports, but getting them to stay exactly aligned might prove difficult.

The solution MIGHT be to export your data to Excel, which doesn't have the same limits because it does its printer graphics differently than Access does. You can get Excel sheets that are very wide and they can be made to print across multiple pages. They just won't look as pretty as an Access report can look.

 

[June7]

As Plog indicated, issue quite possibly is due to un-normalized structure. If table has so many fields cannot fit on 22-inch wide sheet, arrange records so they are multi-line. It can be awkward to read if not done well but I have done it.

 

[waleedimtiaz]

Zip and post a copy of your database.

Note: "IncidentReport1" and "IncidentReport2" are the two reports which I want to combine and have as a 4-page report in my Print Preview.

 

[waleedimtiaz]

OK, there is a limit here that you DO need to know about. Everything in report layout is based on sections, and sections have a limit of 22.75 inches high and wide. For standard 8.5x11 paper, if you have 1-inch margins all around, you have (8.5 inches - 2 x 1 inch margins=) 6.5 usable inches... x 4 = 26 inches. You cannot make a 4-page-wide report on portrait mode that uses the entire 4th page because the section can't be that wide. Section size is a hard limit that you will have a devil of a time in breaking. Note also that if you have things in landscape mode, that's (11 inches - 2 x 1 inch margins =) 9 usable inches and there, x 3 pages is 27 inches - again, over the section limit.

The basis for this limit is that Access uses a unit called twips for placement of objects. The unit is such that 1440 twips = 1 inch in either direction - horizontal or vertical. The slot that holds the width or length of a section and the slots that hold horizontal and vertical positions are limited to 16 bits AND cannot be negative, so the effective limit is 0 to 32767 as the largest positive 16-bit integer. If you do the math, 22.75*1440 = 32760, which is why you cannot get a section to go any wider or longer. If your design absolutely requires 4 sheets across (or 3 in landscape), you have a major problem using Access. Not impossible, as you COULD try to make two or three parallel reports, but getting them to stay exactly aligned might prove difficult.

The solution MIGHT be to export your data to Excel, which doesn't have the same limits because it does its printer graphics differently than Access does. You can get Excel sheets that are very wide and they can be made to print across multiple pages. They just won't look as pretty as an Access report can look.

I do appreciate your thorough response. Is there any way if I can make 2 separate reports and somehow get them all together as a single 4-page report in Print Preview?

 

[Minty]

As others have hinted your database is completely un-normalised.

Just as a pointer (And everytable need to be thought back and redesigned) - Your incident causes should be a table with a CauseID and a field IncidentType. Your ID fields and relationships are incorretly set up.

Unfortunatel, you have effectively dumped a load of spreadsheet data directly into access tables, and it doesn't (won't) work like that.
Please have a read up about normalisation.

 

[arnelgp]

you can't have all your tables Join on Autonumber field?

 

[waleedimtiaz]

As others have hinted your database is completely un-normalised.
View attachment 106427

Just as a pointer (And everytable need to be thought back and redesigned) - Your incident causes should be a table with a CauseID and a field IncidentType. Your ID fields and relationships are incorretly set up.

Unfortunatel, you have effectively dumped a load of spreadsheet data directly into access tables, and it doesn't (won't) work like that.
Please have a read up about normalisation.

I intentionally didn't used the field type "InicdentType" because I wanted to have "CheckBoxes" for each type of accident.

Secondly, I used the primary key as Accident ID, and then related all table ID fields with that "Accident ID", so that for Accident ID#1 I can have all the other information

you can't have all your tables Join on Autonumber fi

 

[waleedimtiaz]

you can't have all your tables Join on Autonumber field?

Why is that?

 

[arnelgp]

Why is that?

have you read anything about database before you program?

 

[Minty]

I'll try not to be blunt here but Access is not a spreadsheet.
You are using fields to store data, this doesn't work.
You can present the data using a check box if you want but you can't store your data as a field.

Using your current model, what happens when a new incident type crops up?
I'll tell you - you have to add a field to your table. Rebuild all your forms, and all your reports.
Does that sound like a well-designed system to you?

 

[waleedimtiaz]

I'll try not to be blunt here but Access is not a spreadsheet.
You are using fields to store data, this doesn't work.
You can present the data using a check box if you want but you can't store your data as a field.

Using your current model, what happens when a new incident type crops up?
I'll tell you - you have to add a field to your table. Rebuild all your forms, and all your reports.
Does that sound like a well-designed system to you?

I totally understand your point. At start, I actually did make a field named "Incident Type", but I wanted to have the incident types as check boxes, but Access only allows Yes/No check boxes. I want to have the following checkboxes beneath the Field "Unsafe workplace conditions", how do I achieve that?

Secondly, what about the reports? How can I form a 4 page report for a single record?

 

[arnelgp]

How can I form a 4 page report for a single record?

fix first your table.
with current setup, most likely, nothing or unrelated information will be shown on the report.

 

[Minty]

Forget about the presentation of data.
At the moment you have a completely unusable table structure, as has been borne out by your need to display 100's of fields on a report.
Those fields have to be data points not fields. You then display them as data points, of which you can have thousands on a report.

You are running against Usain Bolt here and your shoes are on the wrong feet and pointing backward with the laces tied together.
Only one person is going to win that race.

 

[The_Doc_Man]

Is there any way if I can make 2 separate reports and somehow get them all together as a single 4-page report in Print Preview?

You can only open one report at a time in a single Print Preview pane. You can open TWO Print Preview panes because Access can open many things at once. However, a single child window (such as a Print Preview pane or a table in datasheet view) can only contain a single entity or object. That object, if it is a form or report, cannot place data on a displayed section wider than or taller than 22.75 inches. You can have several large sections in vertical sequence but you cannot conjoin sections horizontally beyond the width limit, because the "blank slate" on which you are placing your sections also has that same limit.

If this is something you merely WANT to have, you might have to sacrifice a bit. If this is something someone else (like, perhaps, your boss) MUST have, you have a serious problem that Access will not be able to solve by itself.

Having a non-normalized data set is another problem that others have correctly mentioned. I will not "jump on your stuff" for that problem, as they have made it clear that you have a design issue. I WILL suggest that you have TWO problems that might be about the same subject but they are separate problems that should be addressed separately.

Presentation (your 4-page-wide report) is one thing; data storage and manipulation is another. The others have talked about normalization. You use database normalization as a way to prevent huge problems later, regarding the need to maintain proper relationships among your various data sources and types. The kind of problems you would expect include the inability to track things that should be but are not related to each other.