Others

How to Combine Access Queries Without Duplicating Farmer Records

Maira Mehtab Sep 27, 2026 869 views

Question details

The user needs to display crops and livestock associated with specific farmers without creating duplicate rows.

Product: Microsoft Access
Device & OS: not provided
Scenario: Creating a comprehensive report combining multiple one-to-many data relationships tied to a single entity.
Observed behavior: Joining the crop and livestock queries directly by FarmerID multiplies the rows, resulting in many-to-many duplication in the dataset.

Before you start

Ensure you have your base tables or queries for Farmers, Crops, and Livestock properly set up, with 'FarmerID' existing as a common field in all of them.

Solution 1Recommended

Use a Parent Report with Separate Subreports

Instead of using a flat query, use isolated subreports within a grouped parent report to prevent Cartesian product duplication.

When joining multiple tables that have a one-to-many relationship with a primary table, a standard SQL join will multiply the records. The most effective way to display this in Access is by handling it at the presentation level using a main report and multiple subreports.

Create the Parent Report

Build a new report based on your primary Farmer table or query. Set the report to group the data by the FarmerID field.

Insert Subreports

Navigate to the Detail section of the parent report. Insert two separate subreport controls: one for the Crops query and another for the Livestock query.

Link Data Fields

Select the first subreport and open the Property Sheet. Set both 'LinkMasterFields' and 'LinkChildFields' to FarmerID. Repeat this exact process for the second subreport.

Apply Optional Sorting

If you require the farmers to be listed in a specific order (like alphabetical by name), add sorting rules above the FarmerID grouping level in the parent report's Group, Sort, and Total pane.

Structure Maintained: By keeping the crops and livestock in their own subreports, the database engine processes them independently, completely avoiding the duplication caused by a standard join.

Free Microsoft Office alternative

Manage Data Easily with WPS Office

While WPS Office does not include a direct relational database tool like Microsoft Access, WPS Spreadsheet is a powerful, lightweight alternative for analyzing, filtering, and summarizing large datasets exported from databases.

1. Export Access Data: Export your Access tables or queries into CSV or Excel (.xlsx) format.
2. Open in WPS Spreadsheet: Launch WPS Office and open your exported data files in WPS Spreadsheet.
3. Analyze Data: Use Data > PivotTable to group and summarize your records without complex SQL queries.

Highly compatible with Microsoft Excel (.xlsx) formats for easy data export.Robust PivotTable features to summarize complex, multi-layered data.Free, lightweight software with a familiar tabbed interface.Seamless migration for all your Word, Excel, and PowerPoint documents.

microsoft office alternative - wps office

Frequently Asked Questions

Why does joining multiple Access queries duplicate my data?

When you join two separate one-to-many relationships (e.g., one farmer to many crops and many livestock types) in a single query, Access creates a Cartesian product. This multiplies the rows, showing every possible combination of crops and livestock for that farmer.

Where do I find the LinkMasterFields property in Access?

Open your report in Design View, click on the edge of the subreport control to select it, and press F4 to open the Property Sheet. You will find LinkMasterFields and LinkChildFields under the Data tab.

Can I prevent this duplication in a standard query instead of a report?

It is difficult to represent independent many-to-many relationships in a single flat datasheet view without duplication or empty cells. For exporting, you might use a UNION query, but subreports are the standard solution for presentation.

Can WPS Office open Microsoft Access (.accdb) files?

No, WPS Office specializes in word processing, spreadsheets, presentations, and PDFs. To work with your Access data in WPS, export the tables or queries to an Excel format (.xlsx) and open them in WPS Spreadsheet.

Office Quick Solutions