Others

How to Prevent Double or Triple Results in an Access Query

Huma Ashraf ChHuma Ashraf Ch Oct 14, 2026 870 views

Question details

The user needs to stop their queries from returning duplicated rows that cause calculated aggregate totals to multiply and inflate.

How to Prevent Double or Triple Results in an Access Query
Product
Microsoft Access
Device & OS
not provided
Scenario
Joining a main table to multiple detail queries or tables before calculating aggregate totals, particularly when there are multiple matching records for the same date.
Observed behavior
The query result multiplies the rows (Cartesian product), which produces double or triple results and inflates the final aggregate values.
Before you start

Identify the specific tables or detail queries that share the common date field and check them for multiple matching records before modifying your main query structure.

Solution 1Recommended

Aggregate Source Queries Before Joining

Group and aggregate your data in separate queries first to ensure only unique rows are passed to the final join.

When tables or queries with multiple matching records (like multiple entries for the same date) are joined directly, the result creates a Cartesian product. This multiplies the rows and inflates any calculated totals. Aggregating the sources before joining ensures that only one row per date is passed to the main query.

1
Create a new query for the first data source

Open your database, navigate to the Create tab, and select 'Query Design'. Add your first data source to the design grid.

2
Enable the Totals function

Add the date field and the numeric fields you want to calculate. Click the 'Totals' button (the Sigma icon) on the Query Design ribbon to display the Total row.

3
Set the grouping and aggregation

In the Total row, leave 'Group By' under the date field. Change the Total row under your numeric fields to 'Sum' (or your desired aggregate function).

4
Save and repeat

Save this query with a descriptive name. Repeat this exact process for your second data source so that both are aggregated by date.

5
Create the final joined query

Start a new query and add the newly saved aggregate queries. Join them by dragging the date field from one query to the other, then add the calculated fields to the grid to view your accurate, unmultiplied results.

Aggregate Source Queries Before Joining
Best Practice: Pre-aggregating data not only prevents inflated totals but also significantly improves query performance in large databases.
Free Microsoft Office alternative

Looking for a Lightweight Office Suite?

While Microsoft Access is used for complex database queries, you might find yourself struggling with the heavy bloat and subscription costs of the broader Microsoft Office suite. Consider WPS Office for your everyday document, spreadsheet, and presentation needs. It is completely free, highly compatible, and incredibly easy to use.

  1. 1. Download the Installer: Navigate to the official WPS Office website and click the 'Download' button to get the free installation package.
  2. 2. Install WPS Office: Run the downloaded file and follow the simple on-screen instructions to install the suite in minutes.
  3. 3. Open Your Files: Launch WPS Office and instantly open your existing spreadsheets and documents to continue working without interruption.
Seamless compatibility with Microsoft Excel (.xlsx), Word (.docx), and PowerPoint (.pptx) formats.Lightweight application design that installs quickly and runs smoothly on older operating systems.Completely free for everyday office tasks, reducing your software expenses.Familiar, intuitive tabbed interface that requires zero learning curve when migrating from MS Office.
microsoft office alternative - wps office

Frequently Asked Questions

Why are my Access query totals inflated when joining tables?

Totals inflate when you join tables that have multiple records for the same key (like multiple entries for a single date) without aggregating them first. This creates a Cartesian product, causing Access to multiply the rows and sum the same values multiple times.

What is the difference between a joined query and a subquery in Access?

A joined query links separate, previously saved queries or tables together in the Access query design grid. A subquery is a nested SQL statement embedded directly within the SELECT, FROM, or WHERE clause of a single primary query.

How do I group records by date in an Access query?

Open your query in Design View, click the 'Totals' button (the Greek Sigma symbol) on the ribbon, and select 'Group By' in the Total row underneath your date field.