Data Import & Export

How to Combine Multiple Access Queries into One Excel Worksheet

Elise Williams Oct 22, 2026 868 views

Question details

The user needs a way to automatically consolidate the results of multiple database queries with identical column structures into a single Excel worksheet, rather than exporting each query manually.

Combine Multiple Access Queries into One Excel Worksheet

Product: Excel
Device & OS: not provided
Scenario: Exporting multiple datasets from a Microsoft Access database to an Excel file for reporting and analysis.
Observed behavior: Currently, each query is exported separately or manually copied and pasted into one worksheet, which is highly inefficient when dealing with dozens of queries.

Before you start

Ensure that all the queries you intend to combine have the exact same number of columns in the identical order, and that the corresponding columns share compatible data types.

Solution 1Recommended

Merge Datasets Using an SQL UNION Query

Use a UNION or UNION ALL SQL statement in your database to merge all individual query results into a single dataset prior to exporting it.

A UNION query combines the result sets of two or more SELECT statements into a single, unified result set. This allows you to handle the data consolidation inside the database, so you only have to perform one export operation to Excel.

Open Query Design

In Microsoft Access, go to the 'Create' tab on the ribbon and click 'Query Design'. Close the 'Show Table' dialog box without adding any tables.

Switch to SQL View

Click the 'View' dropdown button in the top-left corner of the ribbon and select 'SQL View'.

Write the UNION Statement

Type the SQL statement to combine your queries. For example: `SELECT * FROM Query01 UNION SELECT * FROM Query02 UNION SELECT * FROM Query03;`. If you want to keep duplicate rows, use `UNION ALL` instead.

Run and Verify

Click the 'Run' button (the red exclamation mark) in the Design tab to execute the query and ensure the data combines correctly.

Export to Excel

With the combined query saved and selected, navigate to the 'External Data' tab, click 'Export to Excel', and follow the prompts to save your single consolidated worksheet.

UNION vs. UNION ALL: The standard UNION command automatically removes duplicate rows across the combined queries. If you need to preserve every single record including duplicates, you must use UNION ALL.

Free Microsoft Office alternative

Analyze Your Exported Database Records with WPS Office

After combining and exporting your Access queries, you can effortlessly view, format, and analyze the resulting dataset using WPS Spreadsheet. As a comprehensive and lightweight alternative, it integrates perfectly into your workflow without the high costs of traditional office suites.

1. Download and Install: Download WPS Office for free from the official website and install it on your computer.
2. Open WPS Spreadsheet: Launch the WPS Office application and select 'Spreadsheet' from the main dashboard.
3. Open Exported File: Click 'Open', navigate to the location where you saved your exported database file, and double-click to start analyzing your combined data.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formats.Powerful data analysis tools, including pivot tables and advanced filtering.Lightweight application that instantly opens large exported database files.Free and familiar interface, requiring no learning curve to master.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Data type mismatch' error when running my UNION query?

This error occurs because the columns being merged across your queries have conflicting data types (e.g., mixing text with numbers). Check the design of each underlying query to ensure the data types in corresponding column positions match exactly.

Do the columns need to have the same names in every query?

No, the column names do not need to match across the different queries. However, the number of columns and their data types must be identical. The final combined dataset will automatically use the column names from the very first query in your UNION statement.

Can I sort the combined results before exporting them to Excel?

Yes, you can apply an ORDER BY clause to sort the combined data. You must place the ORDER BY statement at the very end of your entire UNION query (e.g., `... UNION SELECT * FROM Query03 ORDER BY [ColumnName];`).

Excel & Data Problems