How to Append UNION Query Results to an Access Table
Question details
The user needs a method to take the combined daily results of a UNION ALL query across multiple shifts and save them into a persistent historical summary table for seven days.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Consolidating missed check records from different shift tables daily and storing them into a single historical tracking table.
- Observed behavior
- The user successfully created a UNION ALL query but needs a mechanism to continuously log these daily query results into another static table without overwriting previous data.
Ensure that your destination summary table is already created and that its field data types perfectly match the output columns of your UNION ALL query to prevent type mismatch errors.
Create an Append Query Based on the UNION Query
Use the Access Query Design tool to build an Append Query that reads from your existing UNION ALL query and inserts the records into the history table.
An Append Query is the most direct way to move data from a dynamic select or union query into a static table. You can run this manually each day or set it up for automation.
Open your Microsoft Access database. Go to the 'Create' tab on the ribbon and click on 'Query Design'.
In the Show Table dialog, switch to the 'Queries' tab, select your saved UNION ALL query, click 'Add', and then close the dialog.
On the Query Design ribbon, click the 'Append' button. A dialog box will appear asking for the destination table.
Choose your daily summary/history table from the 'Table Name' drop-down list and click 'OK'.
Drag the fields from your UNION query into the design grid. Ensure the 'Append To' row in the grid matches the correct target fields in your summary table.
Click the 'Run' button (the red exclamation mark) on the ribbon to execute the append. Save this new query with a descriptive name, such as 'AppendDailyShiftData'.
Automate the Daily Append Process
Create an Access Macro to automatically run your Append Query, removing the need for daily manual execution.
Normalize Your Database Structure
Redesign your table structure to avoid the need for complex UNION queries and daily appends altogether.
Analyze Exported Database Data with WPS Office
While Microsoft Access is a specialized tool for relational databases, you can easily export your historical summary tables to Excel formats and analyze them for free using WPS Spreadsheets. WPS Office provides a highly compatible, lightweight alternative for all your everyday document, spreadsheet, and presentation needs.
Frequently Asked Questions
Can I write an Append Query using SQL instead of the Query Design grid?
Yes. In the Access SQL View, you can write an INSERT INTO statement that selects from your UNION query. The syntax looks like this: INSERT INTO SummaryTable (Field1, Field2) SELECT Field1, Field2 FROM (SELECT Field1, Field2 FROM Table1 UNION ALL SELECT Field1, Field2 FROM Table2).
Why am I getting a data type mismatch error when appending records?
This happens when the data types generated by your UNION ALL query do not perfectly match the fields in your destination table. Open the destination table in Design View and ensure that fields like dates, numbers, and short texts align exactly with the query outputs.
How do I prevent appending duplicate records if I accidentally run the query twice?
You can prevent duplicates by establishing a primary key or a unique index in your history table (for example, a composite key combining the Check ID and Date). If the query attempts to append an existing combination, Access will block the duplicate row.
