Combine/Merge Tables in Access 2010
March 5, 2011
The following is a very basic merge, it assumes the data structure is the same for all combined tables.
- Select the first table within Tables Pane
- Copy the selected table (CTRL+C)
- Paste the copied table into the Tables Pane (CTRL+V)
- The Paste Table As dialog will appears
- Ensure the Structure and Data option is selected, select OK
- Open the newly copied table in Design View (Right-click > Design View)
- Drop all AutoNumber fields, Primary Keys, and Foreign Keys
- Save the changes to the copied table
- Copy the new table (CTRL+C)
- Paste the new table into the Tables Pane (CTRL+V)
- The Paste Table As dialog will appear again
- Type the name of the table that will recieve the copied data
- 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!
GingerJune 22, 2011 at 10:44:29 am
What do you mean by "drop" on step 7?
what do I do there?
Jason HoldenJune 22, 2011 at 1:17:32 pm
@Ginger
DROP is SQL lingo
DROP = delete/remove
chuckOctober 4, 2011 at 5:14:30 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?
Jason HoldenOctober 4, 2011 at 6:17:39 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/examples-of-union-queries-mdb-HP005188052.aspx
Hope that helps.
SteveApril 11, 2012 at 10:18:00 am
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
Jason HoldenApril 12, 2012 at 12:53:21 pm
@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.
KonstantinOctober 30, 2012 at 5:40:47 pm
Here a link to a tool that helps to automate this task. It is called AccdbMerge: https://sites.google.com/site/accdbmerge/
missbadJanuary 13, 2013 at 2:17:56 pm
How do you merge/combine two columns. And try to put your answer in simple terms. Thank you.
Jason HoldenJanuary 14, 2013 at 8:26:20 am
@MissBad, sorry but I do not know of an easy way to combine column data. Typically I would do it by creating a new column and executing a query to combine the data from two or more columns into a third column. The query to combine the two columns would be specific to your data requirements.
9 Comments