How to Print Three Invoice Copies With Different Footers in Access
Question details
The user needs to configure a Microsoft Access report to automatically print three one-page copies of a single invoice, with each copy displaying a unique footer label.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Printing categorized copies of an invoice (e.g., Customer Copy, Accounts Copy, Service Filing Copy) directly from a database report.
- Observed behavior
- By default, Access prints a single copy of a report record. The goal is to generate three separate copies per invoice, distinctly labeled at the bottom, and properly paginated.
Ensure your base invoice report is formatted to fit on a single page, and back up your Access database before modifying the record-source query.
Use a Copies Table and Cartesian Query to Duplicate Records
This method forces Access to generate three copies of the same invoice by cross-joining the report data with a standalone table containing your custom footer labels.
By creating a table that isn't joined to your main invoice table, Access performs a Cartesian product (cross join). This multiplies every invoice record by the number of rows in your new table, allowing you to bind different footer text to each duplicated copy.
In Access, create a new table named 'Copies'. Add two columns: one for the footer text (e.g., Customer Copy, Accounts Copy, Service Filing Copy) and one for a SortOrder value (1, 2, and 3) to dictate the printing sequence.
Open your report's underlying record-source query in Design View. Add the new 'Copies' table to the query, but do not create any relationship lines (joins) between it and your other tables. Add the footer text and SortOrder fields to the output grid.
Open the report in Design View. In the Group, Sort, and Total pane, ensure the report is sorted first by the Invoice Number, and then by the new SortOrder field.
Add a text box to your report footer (or page footer). Open its Property Sheet and set its Control Source to the footer text column from your 'Copies' table.
Select the Detail section of your report. In the Property Sheet, set the 'Keep Together' property to Yes. Adjust the height of the section so it spans a full page, forcing each new copy to start on a fresh page. If using page numbers on a one-page invoice, replace the dynamic page number formula with the literal text 'Page 1 of 1'.
Create and Manage Invoices Easily with WPS Office
While Microsoft Access requires complex queries and cross joins to print customized invoice copies, you can simplify your workflow by using WPS Spreadsheet or Writer. WPS Office offers a free, lightweight suite with thousands of ready-to-use templates fully compatible with Microsoft Office formats.
- 1. Download and Install: Get WPS Office for free and install it on your device.
- 2. Choose an Invoice Template: Open WPS Spreadsheet, go to the Template Library, and search for 'Invoice'.
- 3. Customize and Print: Duplicate the invoice sheet for your Customer, Accounts, and Service copies, customize the footers, and print them instantly.
Frequently Asked Questions
Why shouldn't I join the Copies table to my main invoice table?
If you join the tables, Access will look for matching data and won't duplicate the records. Leaving the table unjoined creates a Cartesian product (cross join), intentionally multiplying each invoice by the three rows in the Copies table.
Does this method work for invoices that span multiple pages?
This method is best suited for invoices that are strictly one page long. For multi-page invoices, forcing new pages and maintaining accurate 'Page X of Y' numbering across multiple duplicated copies becomes highly complex and typically requires custom VBA code.
How do I ensure the Customer copy always prints first?
This is controlled by the SortOrder column you added to the Copies table. By assigning 'Customer Copy' a SortOrder of 1, and sorting by this field in your report's Group/Sort settings, the Customer copy is guaranteed to print first.
