Others

How to Copy Multiple Fields into Rows with UNION ALL in MS Access

Adam Davis Oct 10, 2026 869 views

Question details

The user needs to copy a lot number and multiple associated name fields from a single row into separate individual rows in a new target table.

How to Copy Multiple Fields into Rows with UNION ALL in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Normalizing a flat data table by unpivoting multiple columns (eight name fields) into separate rows without writing a complex VBA loop.
Observed behavior: Instead of having 8 name columns in one record, the user wants to create up to 8 separate rows per source record in a new table named NAMES.

Before you start

Before running an append query, ensure you make a complete backup of your Access database and verify that the target table fields have data types compatible with your source data.

Solution 1Recommended

Use an Append Query with UNION ALL

You can normalize your data by combining multiple SELECT statements into one append query using the UNION ALL operator. This avoids the need for a VBA loop.

By writing a structured SQL query, you can isolate each name field alongside its corresponding lot number. Stacking these queries with UNION ALL merges the results vertically. Wrapping this in an INSERT INTO statement directly appends the unpivoted data into your target table.

Create a New Query in SQL View

Open your Access database, navigate to the Create tab on the ribbon, click Query Design, close the Show Table dialog, and switch to SQL View.

Write the INSERT INTO Statement

Begin your query by defining the target table and the specific columns. For example: INSERT INTO NAMES (LotNumber, PersonName)

Construct the First SELECT Statement

Add your first data selection, ensuring you exclude empty records: SELECT LotNumber, Name1 FROM SHEET1 WHERE Name1 Is Not Null

Chain Remaining Fields with UNION ALL

Add UNION ALL on the next line, followed by the next SELECT statement: SELECT LotNumber, Name2 FROM SHEET1 WHERE Name2 Is Not Null. Repeat this pattern for Name3 through Name8.

Run the Append Query

Add a single semicolon (;) at the very end of your query block. Click the Run button (the red exclamation mark) in the Query Design ribbon to execute the append operation.

Handling Empty Fields: Adding the 'Is Not Null' criteria ensures that if a record only has 3 names, it will only create 3 rows in the new table rather than appending 5 blank rows.

Free Microsoft Office alternative

Transform and Analyze Data Easily with WPS Office

While Microsoft Access is used for complex relational databases, a vast amount of data unpivoting, transformation, and analysis can be accomplished faster in a spreadsheet. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Office, equipping you with intuitive data management tools without the need to write custom SQL.

1. Download WPS Office: Download and install WPS Office from the official website to your device.
2. Export Access Data: Export your Access table as an Excel (.xlsx) or CSV file.
3. Open and Analyze: Open the exported file in WPS Spreadsheet to easily manage, unpivot, and analyze your data.

Free and lightweight office suite with fast startup timesHigh compatibility with Microsoft Excel spreadsheet formats (.xlsx, .xls, .csv)Familiar user interface requiring zero learning curvePowerful built-in data sorting, filtering, and pivot table features

microsoft office alternative - wps office

Frequently Asked Questions

What is the difference between UNION and UNION ALL?

A standard UNION query automatically checks for and removes duplicate rows from the final result set, which consumes more processing power. UNION ALL simply appends all rows, including duplicates, making it noticeably faster and generally preferred when transforming this type of structured data.

Is there a limit to how many SELECT statements I can link with UNION ALL in Access?

Yes, Microsoft Access allows up to 50 SELECT statements within a single UNION query. Since this scenario requires linking 8 statements (one for each name field), you are well within the safe operational limits.

Can I use a VBA loop instead of a UNION ALL query?

Yes, you can use VBA with DAO or ADO Recordsets to loop through each record's fields and append them one by one. However, using a single UNION ALL append query is highly recommended because it is executed directly by the database engine, resulting in faster performance and much simpler maintenance.

Office Quick Solutions