How to Merge Tables in Access
Microsoft Access 2013 databases consist of tables that you can change in a variety of useful ways. For example, if your database contains two similar tables, you don't have to spend ages copying data manually to merge them. A special command called an Append query automates the task by selecting data from one table and copying it to another quickly. You even have the ability to choose the records you'd like to copy from your source table and append in the destination table.
Open an Access database that contains a table you want to merge into another table. Ensure that the data types in the source and destination tables are compatible. For instance, if the first field in the source table is a number field, the first field in the destination table must be a number field. As Microsoft notes, "Text fields are compatible with most other types of fields." In other words, it's okay if a field in the source table is a number field and the corresponding field in the destination table is a text field.
Click "Create" and then click "Query Design" to view the Show Table window that lists the database's tables. Click the table that contains the records you wish to copy. Click "Add" followed by "Close." Excel places the table and its fields in the query designer.
Double-click the asterisk at the top of the query designer and Excel adds all the table's fields to the query design grid.
Click "Run" to run the query and view a table that shows the table's records. Click “View” followed by “Design View” and then click “Append” to open the Append window.
Click “Current Database,” if the destination table is in the current database and then click the “Table Name” combo box. Select the table to which you wish to append the source table’s records. Otherwise, click “Another Database” and type the name and location of the database that contains the destination table. Type that table’s name in the “Table Name” combo box and click “OK.”
Right-click the top of the query design window and select “Datasheet View” to view a preview showing the records the query will append. Right-click the top of the window again and select “Design View.”
Click “View” followed by “Data Sheet and then click “Run.” A dialog window asks if you’d like to append the rows from the source table to the destination table. Click “Yes” to do that or click “No” to cancel the operation. When you click "Yes," Excel merges your tables.
Tips & Warnings
- It’s possible that your append query operation will not run. If nothing happens after you click “Run,” check the status bar at the bottom of Excel and look for a message that reads “This action or event has been blocked by Disabled Mode.” If you see that message, click “Enable Content” located in the Message bar.
- If you'd like to create a backup copy of your destination table before merging data into it, right-clicking the table’s name in the Tables panel and select "Copy." You can then right-click an empty space in that panel and select "Paste" to paste a copy of the table. Backups ensure that you don't lose critical information if a problem occurs during a database change.
- This example illustrates merging all the records from a source table into a destination table. You also have the option to choose the records you want to copy into the destination table. After you select a table from the Show Table window, don’t click the asterisk to choose all of the table’s fields. Instead, double-click every field to add it to the query grid. When you’re done, each field appears in a separate column in the grid. You can then type selection criteria into the Criteria text box next to any field. For instance, if you want to only merge records where the totalSales field is greater than 200, "type > 200" (without the quotes) in the Criteria text box next to the totalSales field. Consult your Access help documentation to learn how to apply other types of criteria to queries.