3 Ways To Merge Tables In Access Database

Sometimes the process of copy and paste is not a good option to transfer content. Mainly, when you have a huge amount of data in several tables of the Access database.

To do such kind of task in an effortless way the option of merging Access database table is provided. Using this functionality you can keep your database in an organized way.

Access database offers 3 ways to merge tables in Access or consolidate rows/columns in primary Access database tables. In this blog, you will get step-by-step instructions on how to perform all these methods of merging Access database tables.

Why The Need Of Merging Data In Access?

Merging data in Access actually helps you to make an effortless overview of a huge amount of data. Suppose, you are getting input from different sources, like from entire of your account executive group members. Well, it’s easy to make a report from a single file and this can easily be done by using the simple technique of copy and paste. 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 Append Method

We all know the Access database mainly consists of tables. So if your database is having two similar tables, then without going through the lengthy manual procedure you can easily merge it 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 which 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 is having a number field and the destination table is having 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 are getting listed.
• Hit the table which contains those 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 table 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 common value 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.

Wrap up:

Meanwhile merging tables in Access database if unfortunately, you lost the table, its record or any other stuff then also you have the easy option of data recovery.  Want to know what is it..?

Well for the situation you must try the Access database repair & recovery tool. wth this professional tool, you can make easy recovery of all your lost access database data. Apart from this if your data is lost due to the occurrence of some corruption issue in the table then also this tool works very efficiently.