Copying old records into a new table

[Task Database Nightmare]

Is there an easy way to move records from a table to a new table - but in the
new table, some of the field names have changed due to a redesign, several
field names have been added and several have been deleted.

This table is used to support several queries, forms and reports in a
switchboard format.

 

[Dirk Goldgar]

Is there an easy way to move records from a table to a new table - but in
the
new table, some of the field names have changed due to a redesign, several
field names have been added and several have been deleted.

This table is used to support several queries, forms and reports in a
switchboard format.

Create an append query based on the old table, specifying the new table as
the target. Drag the fields from the old table to the query grid. If the
target table has fields with the same names, those fields will automatically
be filled in, in the "Append To:" line of the query grid. If not, fill in
the new field names yourself in that line. Any fields that you don't want
to copy to the target table can be deleted from the grid, or else just not
have any target field specified in the "Append To:" line.

If the target table contains data, make a backup copy of it, in case this
goes wrong. Then run the append query. Done.

 

[Ken Sheridan]

Provided the data types of the fields whose names have changed are still the
same you can use an append query based on the old table to insert rows into
the new. In query design view add only those fields which are still used by
the new table, even if under a new name, to the design grid. Then in the
'append to' row of each column enter the name of the field in the new table
into which you want the values from the field in the old table to go. Where
the names are the same the names should already be there automatically, so
its only the ones which have changed which you'll need to enter, which you
can do by selecting the name from the 'append to' row's drop down list.

One thing you need to watch out for is if an autonumber field has been used
as the primary key. You can append values from this to an autonumber field
in the new table provided that the new table does not contain a row which
already uses the same value in the autonumber column as that in the old
table. Alternatively you can allow the new table to generate new autonumber
values for each row automatically by not appending this field at all. If
there are other related tables which have foreign key columns referencing the
autonumber field of the old table, however, you need to append the old
autonumber values or the links between the related tables will be broken.

Ken Sheridan
Stafford, England

 

[a a r o n _ k e m p f]

I have to do things like that about 20 times a day.

grow a pair kid.
Learn SQL, or your job goes to India