Extra pages

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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]));

Answer 4

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...

Answer 5

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.