Merging data from one table to another (1 Viewer)

[DrZoidberg]

Hi, I have a db (MS Access 2000) that currently has 37 tables in it, each table holds about 30 rows and 72 columns of data. I need to basically create the entire database again but change all the column names slightly so that they still hold the same information but with different column names, then I need to merge each newly created table into the existing corresponding table.

As there are about 86400 entries I would rather change 2000 odd column names than re-enter all the data again. I would be extremely appreciative if someone could suggest the easiest and quickest way to do this. Many thanks in advance.

 

[Sergeant]

Perhaps I'm missing something. Can't you just open the tables in design view and change the field names?

 

[DrZoidberg]

Sorry, I should have been more specific, I actually need to keep the existing tables with the existing data, duplicate it, change the column names in the duplicated table and then merge the new table with the new column names into the old table with the old table names!

 

[Uncle Gizmo]

First of all, I would think that there is something terribly wrong with your database design for you to be considering this action!

However what you propose could be achieved. I will describe how I think I would do it based on just one table. You will need to do this for all your tables.

Make a backup of your database.....

Make a copy of your database, open the copy and change the table names to something like, old table name "tblMyTable" changed to "tblMyTable2" it is important to keep the names nearly Exactly the same. now go through each table and change all the field names, use the same idea, "fldMyField" becomes "fldMyField2". Close this copy, open the original database and import your renamed tables into the old database. Now highlight "tblMyTable" and invoke the query builder, in the query builder select the other table "tblMyTable2" drag all of the fields from both tables into the query grid and select "make table "from the query options. Run the query and when it asks for a table name, give it the name "tblMyTable3"

This table will have all your data in it twice! Strange!


(If all the tables are of the exact same structure there may be a better way)

 

[DrZoidberg]

Thanks Uncle Gizmo, yes this is the result of a seriously overlooked factor, in an ideal world, I would have designed the database differently, but have learned a life long lesson. All tables are exactly the same structure, just named differently. Thanks very much indeed for your advice any way! If you're around at all would you mind posting the other way? If not no probs.

Cheers

 

[Uncle Gizmo]

I forgot to mention, when you join the two tables together in the query you will have to link them on a unique field. The unique field must contain a unique ID for each record. This is usually accomplished with an ID field. If you do not have such a field, you will have to create one for each table before you Proceed.

Considering the number of tables you have to edit you may wish to do it with an SQL statement. However, using SQL is not straightforward, it will probably take you as long to learn how to do it with SQL, as it will take you to manually edit all the tables. If you went for the SQL route, it would be much more interesting, and you would come away from the experience with more knowledge.

I have a very limited knowledge of SQL, I can usually manage to get done the bits of code I need, but I personally would not be able to help you very much. However there are other forum members with this knowledge and may be able to help you if they have the time.