How to Prevent Double or Triple Results in an Access Query
Question details
The user needs to stop their queries from returning duplicated rows that cause calculated aggregate totals to multiply and inflate.

- 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.
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.
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.
Open your database, navigate to the Create tab, and select 'Query Design'. Add your first data source to the design grid.
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.
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).
Save this query with a descriptive name. Repeat this exact process for your second data source so that both are aggregated by date.
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.

Use SQL Subqueries in the FROM Clause
Write SQL subqueries to group data by date directly within the main query's FROM clause, keeping everything in a single query object.
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. Download the Installer: Navigate to the official WPS Office website and click the 'Download' button to get the free installation package.
- 2. Install WPS Office: Run the downloaded file and follow the simple on-screen instructions to install the suite in minutes.
- 3. Open Your Files: Launch WPS Office and instantly open your existing spreadsheets and documents to continue working without interruption.

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.




