Skip to: Site menu | Main content

Email Facebook LinkedIn Twitter Google

Blog...

Combine/Merge Tables in Access 2010

Posted At : March 5, 2011 10:51 AM | Posted By : Jason Holden
Related Categories: Access

The following is a very basic merge, it assumes the data structure is the same for all combined tables.

  1. Select the first table within Tables Pane
  2. Copy the selected table (CTRL+C)
  3. Paste the copied table into the Tables Pane (CTRL+V)
  4. The Paste Table As dialog will appears
  5. Ensure the Structure and Data option is selected, select OK
  6. Open the newly copied table in Design View (Right-click > Design View)
  7. Drop all AutoNumber fields, Primary Keys, and Foreign Keys
  8. Save the changes to the copied table
  9. Copy the new table (CTRL+C)
  10. Paste the new table into the Tables Pane (CTRL+V)
  11. The Paste Table As dialog will appear again
  12. Type the name of the table that will recieve the copied data
  13. Ensure the Append Data to Existing Table option is selected, select OK

Using this method Access provids no feedback on the results of the merge.

Quick and dirty!

Comments (6) | Print | del.icio.us | Digg It! | Linking Blogs
3578 Views
Comments (Comment Moderation is enabled. Your comment will not appear until approved.)
[Add Comment] [Subscribe to Comments]
What do you mean by "drop" on step 7?
what do I do there?
# Posted By Ginger | 6/22/11 10:44 AM
@Ginger

DROP is SQL lingo

DROP = delete/remove
# Posted By Jason Holden | 6/22/11 1:17 PM
Hello.. I have 4 tables that I want to combine into one table however, all 4 tables only have 5 fields/columns in common. Each of the 4 tables have anywhere from 10-20 more fields/columns that need to be combined into the single table.

Everything that I have read so far about joins/appends/merges require all fields to be the same across all tables to be combined into one.

Is there a way to combine multiple tables that have different fields?
# Posted By chuck | 10/4/11 5:14 PM
@chuck - It sounds like you understand the basics of the syntax you need to use. You should be able to join multiple different tables so long as the field names are the same in the SQL STATEMENT. That means in your SQL statement you need to SELECT only the fields that you want to match, in the order.

Here's a good example on the MS office site:

http://office.microsoft.com/en-us/access-help/exam...

Hope that helps.
# Posted By Jason Holden | 10/4/11 6:17 PM
Question - what if I have duplicate data in my tables? I tried to export into Excel, remove duplicates, and import back in. But...I received some validation issues and if a txt field is too long, it only
brings in a portion of the data.

Thanks,
Steve
# Posted By Steve | 4/11/12 10:18 AM
@Steve: If maintaining the data type is not required you can try importing into a NEW table with all text type fields (each should be as long as necessary to accomodate all the data). I usually remove duplicates by using a query. A DELETE query to do that would be data specific, but I'm assuming there is at least one fields that can be matched to determine a duplicate record. If you're still unable to get that to work please contact me directly and I can possibly take a look for you.
# Posted By Jason Holden | 4/12/12 12:53 PM
[Add Comment]