Copy columns/fields from one table to another

AndyDevine1

Board Regular
Joined
Jun 1, 2006
Messages
172
Hi there

I've created 85 new fields in a table via an import. Now I need to add them to another table

Is there an easy way of doing this?

Thanks

Andy
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Hi,
It's hard to think of a simpler way than repeating the import, now with the other table. Having to create so many fields, if you are doing it repeatedly and in two tables, suggests possibly that your design is not properly normalized so you may find it harder to write queries as a result. You might want to share the table structure(s) here and we may be able to suggest a better way. On the other hand, if it works and you aren't going to be expanding this project then sometimes you just let sleeping dogs lie.
ξ
 
Upvote 0
Hi
If I try to import the (85) fields into the table where I want them I get an error message...."Field 'XYZ' does not existing in the destination table 'ABC_Table'

Any ideas of what I can do?

Thanks

Andy
 
Upvote 0
Hi,
Not sure. I thought you were creating a brand new table during (simultaneously) with the import. If you are importing to an existing table the field must exist in the destination table (i.e., add field XYZ to your table - all 85 fields must exist).
 
Upvote 0
Ah right thanks...yes I need to add 85 new fields to an existing table where they don't exist already....
 
Upvote 0
I don't have the time right now to write such a script for you (and in any case you aren't providing any details whatsoever, such as field names and datatypes). You probably should just get busy and add the fields to the table. If you do want to write a script there are examples of how to work with database objects in code here:

Microsoft Access tips: DAO Programming Code Examples

You can also use SQL DDL as well as DAO (or ADO), although this is not where Access shines. In general, it's simplest to just create your fields by hand. It's typically a little work at first but then it's done and you don't have to worry about it anymore.
 
Last edited:
Upvote 0
Hi

Just coming back to this now...

The data is largely numerical.

As a thought, is there a way of merging the tables?

Thanks

Andy
 
Upvote 0
Hi

Just coming back to this now...

The data is largely numerical.

As a thought, is there a way of merging the tables?

Thanks

Andy

Just a thought here - You could take 1 record(with the field names) plop it into Excel for both Table A and Table B, do a vlookup to see what fields you need to add by comparing table A to table B. Once you get those fields that you need copy/paste that 1 record with just the fields that you need into a separate spreadsheet (we'll call it Table C).

Then import that spreadsheet into Access through the Ribbon -> External Data -> Excel.

Then right-click the tables to access design view for Table A and Table C - you can select all the fields in table C and then just paste it after the last field in Table A. It's important to have a record for Table C because with the import and then copy/paste in design view - it will populate the type of field based on the data as well. (text, numbers etc).

This should take you a few minutes to do.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top