Others

How to Prevent Microsoft Access Queries from Repeating Parent Records

Phi Hung VoPhi Hung Vo Nov 4, 2026 869 views

Question details

The user wants to display parent records only once, with their multiple child records listed underneath, rather than repeating the parent row for every child in a query.

How to Prevent Microsoft Access Queries from Repeating Parent Records
Product
Microsoft Access
Device & OS
not provided
Scenario
Creating a query or report that joins a parent table to a children table in a one-to-many relationship.
Observed behavior
The standard query naturally returns one row for each matching child, causing the parent record data to repeat for every associated child.
Before you start

Ensure your parent and child tables have a properly defined one-to-many relationship, typically linked by a primary key in the parent table and a foreign key in the child table.

Solution 1Recommended

Create a Main Report with a Subreport

This is the most effective design for one-to-many data, allowing you to cleanly nest multiple children records under a single parent display without duplicating data.

A simple flat query joining a parent and child table will naturally return one row per child, duplicating the parent data. To visually display the parent once, you should transition from a raw query datasheet to an Access Report utilizing a subreport.

1
Create the Main Report

Build a new report based solely on the parent table or a query that only contains parent records.

2
Create the Subreport

Build a separate report based on the children table, formatted to clearly display the child details.

3
Insert the Subreport

Open the Main Report in Design View, and drag the Subreport from your navigation pane into the Detail section of the main report.

4
Link the Reports

Select the subreport control, open the Property Sheet, and ensure the 'Link Master Fields' and 'Link Child Fields' properties are both set to the matching ID (e.g., ParentID).

Create a Main Report with a Subreport
Clearer Presentation: Using a subreport keeps your main layout clean while dynamically listing all associated children underneath without repetition.
Free Microsoft Office alternative

Boost Your Productivity with WPS Office

While Microsoft Access is designed specifically for complex relational database management, WPS Office provides an exceptional, free alternative for your daily word processing, spreadsheet, and presentation needs. It is fully compatible with Microsoft Office formats, incredibly lightweight, and highly intuitive.

  1. 1. Download WPS Office: Visit the official WPS Office website and click 'Free Download' to get the installer for your operating system.
  2. 2. Install the Software: Run the downloaded installation file, accept the terms, and follow the simple on-screen instructions to complete the setup.
  3. 3. Start Creating: Open WPS Office and select Writer, Spreadsheet, or Presentation to immediately start working with your Microsoft Office files.
Fully compatible with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight design that runs smoothly on both older and modern devices.Built-in PDF reader and editor for comprehensive document management.Free to download and use with a familiar, tabbed user interface.Easy data formatting and filtering in WPS Spreadsheet for organizing list-based data.
microsoft office alternative - wps office

Frequently Asked Questions

Why does a one-to-many query duplicate the parent record?

In relational databases like Microsoft Access, a standard SQL JOIN operation retrieves a row for every matching record found in the child table. Because multiple children share the same parent, the parent's data is inherently duplicated on each corresponding row to maintain a flat tabular structure.

Can I hide duplicate parent records directly in an Access Datasheet view?

You cannot completely hide duplicate parent rows in a standard flat query Datasheet view. However, you can use the Subdatasheet feature on the parent table itself to expand and collapse child records under a single parent row.

What is the difference between an Access subreport and grouping?

Grouping organizes a single flat query by collecting repeated values and placing them in a group header, leaving child data in the detail section. A subreport embeds an entirely separate report (child data) inside the detail section of another report (parent data), offering greater layout flexibility for complex one-to-many relationships.