Others

How to Combine Multiple Order Columns into One Field in Microsoft Access

Nimra Malik Oct 15, 2026 868 views

Question details

The user needs to normalize a database table by consolidating multiple spread-out order columns (e.g., Order1, Order2, Order3) into a single, unified order field in a new target table.

How to Combine Multiple Order Columns into One Field in Microsoft Access

Product: Microsoft Access
Device & OS: not provided
Scenario: Restructuring an unnormalized Access table to combine repeating column groups into a single column for better querying and data management.
Observed behavior: Data is currently separated into sequentially named columns, making it difficult to analyze, count, or query records efficiently without checking every individual column.

Before you start

Ensure you have created a new, empty target table in your database with the appropriate fields (e.g., an identifying Agent field and a single Order field) to receive the consolidated data.

Solution 1Recommended

Use a VBA Script to Append Columns Automatically

This method is ideal for tables with many similarly named columns (e.g., Order1 to Order10) as it uses a loop to automate the append process.

Using Visual Basic for Applications (VBA) allows you to loop through numbered columns and insert their data into the new table efficiently. This avoids the need to manually create multiple queries or write lengthy SQL statements.

Open the VBA Editor

Press ALT + F11 in Microsoft Access to open the VBA Editor, then insert a new Module from the top menu.

Declare Variables

Set up your variables by declaring a string for the SQL statement (Dim strSQL As String) and an integer for the loop counter (Dim n As Integer).

Create the Loop Structure

Write a For loop that covers the number of your order columns, for example: For n = 1 To 4.

Write the SQL Insert Statement

Inside the loop, construct the SQL statement to select the identifier and the current order column, ensuring you filter out empty records: strSQL = "INSERT INTO CorrectedTable(Agent, [Order]) SELECT Agent, [Order]" & n & " FROM CurrentTable WHERE [Order]" & n & " IS NOT NULL"

Execute the Code

Add CurrentDb.Execute strSQL, dbFailOnError below the SQL string definition, finish the loop with Next n, and run the script to populate your new table.

Use a VBA Script to Append Columns Automatically

Backup Recommended: The dbFailOnError parameter ensures the process stops immediately if an error occurs, preventing partial or corrupted data transfers.

Solution 2

Use Manual Append Queries (No SQL Required)

Best for users who prefer the visual Access Query Design interface and only have a few columns to combine.

Free Microsoft Office alternative

Looking for a Lightweight Office Suite?

While WPS Office does not include a database management tool like Microsoft Access, it offers a powerful, free alternative for Word, Excel, and PowerPoint. If you prefer managing tabular data and normalizing columns in spreadsheets instead of complex databases, WPS Spreadsheet provides robust tools for data consolidation and pivot tables.

1. Download the Installer: Visit the official WPS Office website and download the free installer for your operating system.
2. Install WPS Office: Run the setup file and follow the straightforward on-screen instructions to complete the installation.
3. Manage Data in Spreadsheets: Launch WPS Spreadsheet to organize, filter, and consolidate your tabular data without the need for complex database queries.

Completely free and lightweight alternative to Microsoft OfficeFully compatible with Microsoft Excel (.xlsx) formatsFamiliar user interface for immediate productivityBuilt-in data consolidation and PivotTable tools for easy data management

microsoft office alternative - wps office

Frequently Asked Questions

Can I combine these columns using just standard queries without VBA?

Yes, you can use a Union query to combine the columns with SQL manually, or use the visual Query Design tool to run a separate Append query for each column individually.

How do I handle empty or null values when combining columns?

You should add a 'WHERE [Order] IS NOT NULL' condition to your Append queries or VBA SQL string. This ensures that empty fields in the original table aren't transferred as blank rows in your new target table.

What fields should the new target table contain?

The target table should include any shared identifying fields from the original table (such as an Agent ID or Primary Key) and a single, unified Order column to hold the normalized data.

Office Quick Solutions