Others

How to Create an Access Report with a Contract and Payment Subreport

Phi Hung Vo Oct 1, 2026 868 views

Question details

The user needs to generate a report that displays a long, multipage contract exactly once, followed by a table of multiple payment records associated with that specific contract.

How to Create an Access Report with a Contract and Payment Subreport

Product: Microsoft Access
Device & OS: not provided
Scenario: Designing a database report to handle a one-to-many relationship (one contract to many payments) without duplicating the lengthy contract text for each payment entry.
Observed behavior: When multiple payment records exist for a single contract, the standard report setup causes the entire 40-field contract information to repeat for every single payment row instead of showing up just once.

Before you start

Ensure that your Contracts table and Payments table share a common linking field, such as a primary key 'ContractID' in the Contracts table and a matching foreign key in the Payments table.

Solution 1Recommended

Use an Access Subreport to Display Related Payments

This is the standard and most effective method for handling one-to-many relationships in Access reports, preventing the main record (contract) from duplicating.

By placing the contract fields in the main report and the payment fields in a subreport, Access treats them as separate but linked entities. The subreport acts as a container that loops through the multiple payment records without forcing the main report's detail section to repeat.

Design the Main Report

Open your Access database, navigate to the Create tab, and select 'Report Design'. Add your approximately 40 contract fields into the Detail section of this main report.

Insert the Subreport Control

From the Report Design Tools ribbon, click the 'Subform/Subreport' control icon. Click and drag within the Detail section (or a dedicated Group/Report Footer section) to place the subreport below your contract fields.

Select the Data Source

When the SubReport Wizard opens, choose your Payments table or query as the data source. Select the columns you want to display, such as payment method, date, bank, amount, and payer.

Link the Master and Child Fields

Select the edge of the subreport control to open the Property Sheet. Under the Data tab, ensure 'Link Master Fields' and 'Link Child Fields' are both set to your linking ID (e.g., ContractID). This synchronizes the payments to the currently displayed contract.

Use an Access Subreport to Display Related Payments

Verification: Switch to Print Preview. You should now see the multipage contract printed once, followed seamlessly by a consolidated table of all related payment records.

Solution 2

Use a Word Mail Merge as a Contract Template

If your contract contains heavy standard text and complex formatting, a Word mail merge might be easier to format than an Access report, though it has limitations with repeating data.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Office Tasks?

While Microsoft Access is used for building relational databases, you can handle most contract generations, billing tables, and mail merge tasks much more easily using WPS Office. WPS Writer offers powerful, intuitive Mail Merge features that seamlessly connect to your spreadsheet data without the steep learning curve of database management.

1. Download WPS Office: Visit the official WPS website and download the free suite for your operating system.
2. Prepare Your Data in Spreadsheets: Open WPS Spreadsheets to organize your client and payment data cleanly in a workbook.
3. Use Mail Merge in Writer: Open WPS Writer, draft your contract, and use the References tab to link your spreadsheet and insert merge fields effortlessly.

Completely free and lightweight Office suiteHighly compatible with Microsoft Word and Excel formatsRobust Mail Merge functionality for easy contract generationFamiliar, easy-to-use tabbed interface for quick adoptionBuilt-in PDF tools to finalize and secure your contracts

microsoft office alternative - wps office

Frequently Asked Questions

Why does my entire contract text repeat for every single payment?

This occurs when your report is based on a flat joined query containing both Contract and Payment data. Because there are multiple payments per contract, the query outputs a new row for each payment, duplicating the contract data. Using a main report for the contract and a subreport for payments resolves this.

Where is the best place to put the payments subreport?

You can place the subreport in the main report's Detail section below the contract fields, or in a dedicated ContractID Group Footer. The Group Footer is often preferred if you want to ensure the payment table appears at the very end of the contract data.

What do 'Link Master Fields' and 'Link Child Fields' do?

These properties tell Access how the main report and subreport are related. The Master Field is the primary key in your main report (e.g., ContractID), and the Child Field is the corresponding foreign key in your subreport's data. They ensure the subreport only shows payments belonging to the current contract.

Office Quick Solutions