Want to know how to merge tables in Access database? Combining the data from the databases in Access helps you create an effortless overview of a huge amount of data and keep your database organized. However, Access database offers 3 ways to combine tables or consolidate rows/columns in the primary database tables. In this blog, I will mention the step-by-step instructions for merging Access database tables easily.
Free MS Access Database Repair Tool
Repair corrupt MDB and ACCDB database files and recover deleted database tables, queries, indexes and records easily. Try Now!
By clicking the button above and installing Stellar Repair for Access (14.8 MB), I acknowledge that I have read and agree to the End User License Agreement and Privacy Policy of this site.
Why is The Need Of Merging Data In Access?
Merging data in Access helps you to make an effortless overview of a huge amount of data. Suppose you are getting input from different sources, like from entire account executive group. It’s easy to create a report from a single file, which can be done simply by using the copy and paste technique. But what if there are multiple records and tables are available? In that case, copying and pasting data from a huge number of rows and columns is just not enough.
Thus, for such types of tasks merging Access tables is the best solution to go with.
How to Merge Tables In Access Database?
Method 1# Merge Access Tables Using the Append Method
We all know the Access database mainly consists of tables. So if your database has two similar tables, then without going through the lengthy manual procedure, you can easily merge them by using the append query.
Using this special append query command, you can easily perform the task of merging table data in Access.
Apart from this, you have the option to select the records which you need to copy from the source table and after that append them to the destination table.
Step 1
- Open the Access database that contains the table which you need to merge into another one.
- Make sure that the source and destination table data types are well compatible with each other.
- Suppose, if your source table’s first field is a number, then it’s compulsory for the destination field also to be in the number field.
- According to Microsoft, text fields are well compatible with other field types. So if your source table has a number field and the destination table has a text field then no issue will arise.
Step 2
- Hit the “Create” button and then tap to the “Query Design”. This will open the “Show Table” window here you will see all the database tables listed.
- Hit the table which contains the records you need to copy.
- Hit the “add” and then “close” option. Access database put the fields and table within the query designer.
Step 3
- At the top section of query design, there is an asterisk, you need to make a double click over it. Access will add all tables’ fields within a grid of query design.
Step 4
- Hit the “Run” button to execute the query and also to view the table that has records in it.
- Hit the View>Design View> append option. This will open the Append window.
Step 5
- Make a hit over “Current Database”, if your destination table is present in the current database.
- After that hit the combo box of “Table Name”. Choose the table which you need to merge to the records of the source table.
- Or else, hit the “Another Database” option. After that assign the name and location of your Access database which is having the destination table.
- Enter the name of the table within the Table Name and click the ok button.
Step 6
- Make a right-click over the query design window. After that choose the “Datasheet View” for watching out the preview shown by the records which query will append.
- At the window’s top make rights click and choose the “Design View” option.
Step 7
- Hit the View tab and then select the Data Sheet > Run option.
- In the opened dialog box you are asked to press yes or no to append rows of the Access database from source to the destination table.
- Hit the yes button, to merge tables in Access.
Method 2# Merge Access Tables Using Inner Join
Another method to merge tables in Access is by using the inner join. Here is the complete step to perform this process of merging the Access database table using an inner join.
Usually, an inner join is used to obtain entire records from linked tables. You will also see that a common value is present in both Access database tables.
Steps To Merge Tables In Access By Using The Inner Join Function
Step 1: Create the Tables
- First of all, you need to make a table that you need to link in your Access database.
- Just like I have created here the two tables: one contains information about shipping and another is about the clients.
- Here is a screenshot of the client and shipping table.
- In the end, you need to link the shipping table with the client table using the Client ID field. This field should present in both tables.
Step 2: Link the Tables
- For linking Access database tables, at first, go to the create tab and then hit the Query Design.
- In the opened dialog box of “show table” make a double click on each client and shipping tables. After completing all this, hit the close option.
- Now you will see both table’s respective fields will start appearing on your screen.
- From the client table you need to drag the ‘Client ID’ field. After that put it over the shipping table.
- This process will link the shipping and client table with one common field i.e. ‘Client ID’.
Step 3: Select The Fields To Display
Choose the field which you need to show from your linked tables. For this, you need to make a double click over desired fields of each table.
Suppose, make a double-click over the following fields:
- From the Clients table choose the ‘Client First Name’ and the ‘Client Last Name’
- Now from the Shipping table choose the ‘Shipping Address’
At last hit the run button, as this will display the result:
Now this newly created merged table contains the 3 fields which we have selected from these two linked tables.
After that you will see that in your linked tables 3 fields start appearing. So this is how you can merge Access table data using inner join.
Method 3# Merge Access Table Using Left Joins
For retrieving up the data, you just need to do several right or left joins. Here is an example to show you how it is to be done.
I will mainly focus on how to achieve this query using the left joins.
For this example, we will assume:
First Table is of “Employees”
Second Table is of “Orders”
Third Table is of “Order Details”
- First of all, create one new query and then add all these 3 tables into the new query.
- Make a right-click over the “join line” present in between the table of orders and employee. From the popup menu choose the “join properties”.
- From the opened window of “Join Properties” you have to choose the second option and then hit the OK button.
- In this way, your query will look like. You will see an arrow sign on the right-hand side of the join line present between the Orders and Employees table.
- After that make a right-click over the join line present between the order detail and order table. Now from the popup menu choose the “join properties” option.
- From the opened Properties window you have to select the second option and tap to the OK button.
- Choose the fields which you need to show in the query results. I have selected the following fields:
- So when the query is been executed, you can see the blank values present within the Quantity fields. And Order Date.
This happens because actually there is no matching records available in corresponding tables which mainly uses the join criteria.
Frequently Asked Questions:
Can I Merge Two Tables in Access?
Yes, you can definitely combine two tables in an Access database, even if you have not created relationships. MS Access creates inner joins automatically if you add 2 tables to a query & those tables each have a field with the same or compatible data type, and one of the join fields is a primary key.
How to Join Two Tables in a Database?
If you want to join two tables in a database, you can use the following syntax:
SELECT * FROM table_1 JOIN table_2 USING (id);
Which Key Combines Two Database Tables?
Composite Key combines two database tables.
How to Append Tables in Access?
To append tables in Access, follow the steps below:
- Navigate to Home tab, click on View group >> and click View.
- After that, click on Design View.
- On the Query Design tab, in the Query Type group, click Append.
Final Thoughts
Merging tables in Access is quick and effective. Use Append or Make Table queries for permanent changes. Always test before making major changes. With these steps, your database stays efficient and accurate.
But what if you lost your database data? In such a situation, you can try the Access Database Recovery Tool. With this professional tool, you can easily recover all your lost Access database data. Apart from this, if your data is lost due to the occurrence of a corruption issue in the table, then this tool also works very efficiently.
References:

This software repairs & restores all ACCDB/MDB objects including tables, reports, queries, records, forms, and indexes along with modules, macros, and other stuffs effectively.
- Download Stellar Repair for Access rated Great on Cnet (download starts on this page).
- Click Browse and Search option to locate corrupt Access database.
- Click Repair button to repair & preview the database objects.