question

DeonOlivier-9974 avatar image
0 Votes"
DeonOlivier-9974 asked thedbguy commented

Extra pages

I am printing a report from an Access table. There are 20 records on the same ID. When printing the report in Access, I get a report with 20 pages, all with the same data on the report.
How do I prevent this?

Thanks
Deon

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

Your screenshot does appear to confirm my suspicion that the table is not normalized to Third Normal Form. It would appear that BOMDate, BOMOriginator and BOMDescrciption at least are transitively dependant on the key via BOMID. If it is those data that are being duplicated in the report, then that is only to be expected. The duplication could be suppressed by the use of the DISTINCT predicate, as the dbguy suggested earlier, but that does not address the fundamental design flaw, which can only be corrected by decomposition of the table into two or more correctly normalized tables.

The fact that the duplicated data are printed on two pages of the report is irrelevant. That is merely an artefact of the report design.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

What is the report's RecordSource property? If it is a query, what is the query's SQL statement?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DeonOlivier-9974 avatar image
0 Votes"
DeonOlivier-9974 answered

Hi
Recordsource is a query. The SQL statement as follows:

 SELECT Tbl_BOM.ID, Tbl_BOM.BOMID, Tbl_BOM.BOMDate, Tbl_BOM.BOMOriginator, Tbl_BOM.BOMMatNumber, Tbl_BOM.BOMDept, Tbl_BOM.BOMDescription, Tbl_BOM.BOMBaseQty, Tbl_BOM.BOMBaseQtyUOM, Tbl_BOM.BOMMRPController, Tbl_BOM.BOMCompNumber, Tbl_BOM.BOMCompDescription, Tbl_BOM.BOMQtyExclWaste, Tbl_BOM.BOMPercentScrap, Tbl_BOM.BOM_UOM, Tbl_BOM.BOMIssueSLoc, Tbl_BOM.BOMApprover1, Tbl_BOM.BOMApprover2, Tbl_BOM.BOMApprover3, Tbl_BOM.BOMApproveDate1, Tbl_BOM.BOMApproveDate2, Tbl_BOM.BOMApproveDate3, Tbl_BOM.BOMComplete
 FROM Tbl_BOM
 WHERE (((Tbl_BOM.BOMID)=[Forms].[Frm_BOMChange].[Text1]));
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

What exactly are you trying to prevent? Are all the 20 records on the same ID exactly the same? If so, are you asking how to print only one of them? If so, try adding DISTINCT to your SQL statement. For example,

SELECT DISTINCT...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KenSheridan-7466 avatar image
0 Votes"
KenSheridan-7466 answered

Any report based on that table should print only one instance of each row in the table. If the columns you are printing have identical data for all 20 rows then the table is incorrectly normalized and in need of decomposition.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DeonOlivier-9974 avatar image
0 Votes"
DeonOlivier-9974 answered

Hi all,
The table data looks like this:

144067-capture.jpg



The data are not the same. The query will retrieve the data from the table. The criteria is the "BOMID". It will retrieve 2 rows from the table if the BOMID = 1.
When the report runs, 2 pages are created instead of 1 page. Both pages has exactly the same data. I need to get rid of the 2nd page.

Thanks


capture.jpg (44.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

I see, you want to see two records on page 1, right? You might have a Page Break in the Details section or you have the Force New Page property set to either Before or After Section. Check for those...

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DeonOlivier-9974 avatar image
0 Votes"
DeonOlivier-9974 answered thedbguy commented

Hi all,
Yes, normalising the tables did the trick.

Thanks
Deon

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi. Glad to hear you got it sorted out. Good luck with your project.

0 Votes 0 ·