Copy Data

  • Thread starter Thread starter TLMM
  • Start date Start date
T

TLMM

Hi,

I have 2 Access tables and would like to copy or import the data from one
table to the other table. The old table contains data and the new one does
not have data yet. The new table has all the fields of the old table plus
some more added fields. I would like to copy the data from the old table to
the new table which has more fields. Is there a way to do this? I tried a
few things but no luck.

Thanks.
 
Hi

yes, use an append query

go to the query section
choose create query in design view
choose the "old" table
close the choose table dialog box
under query on the menu choose append query
choose the name of the new table
choose all the fields out of the old query that you want to match up in the
new
on the append to line, match up the field names (if needed)
run the query (!)

note: if the new table contains a PK field that wasn't in the old table and
is not of "autonumber" type this method will probably not work.

let us know how you go.

Cheers
JulieD
 
Have you tried creating an Append query? Begin by selecting New query from
the database window and choosing your Old table as the table the query will
use. Then from the Design View of your query, click Query from the menu and
select Append Query. You will be prompted to select the Table to Append
To: -- select your New table.

Add each field from your Old table to a column in the query grid. Presuming
the fields have identical names in your Old and New tables, Access will
automatically determine the field in the New table to which the data in the
field from your Old table should be appended.
 
Thanks!

I don't know if it worked or not if that makes any sense!. The reason I am
not sure is that when I run the query it looks just like the old table; it
does not add the extra new fields, so I am not sure whether it worked or
not.

In the Append To field in the query, all the fields for the new table are
there if I click the drop-down, but the fields in the new table that don't
have aren't in the old table don't get added to the query.

Alos, it shows the captions from the old table instead of the new table.

Any suggestions?
 
Sorry - it did work. As soon as I opened the table, all the data was there.
I was thinking that it would show up in the query, but I guess that only the
fields with data show up in the query, then when you open the table, all
fields show, those with data and those without.

Thanks for your help!
 
Hi TLMM

all the append query is doing is taking the data out of your old table and
putting it in the new - the way to check that it has worked is to open up
the "new" table itself in datasheet view, you should be able to see the old
records and the headings for the new fields.

is this what you want?

Cheers
JulieD
 
Hi

glad it worked.

cheers
julieD

TLMM said:
Sorry - it did work. As soon as I opened the table, all the data was there.
I was thinking that it would show up in the query, but I guess that only the
fields with data show up in the query, then when you open the table, all
fields show, those with data and those without.

Thanks for your help!
Click to expand...
 
Hi,

I have 2 Access tables and would like to copy or import the data from one
table to the other table. The old table contains data and the new one does
not have data yet. The new table has all the fields of the old table plus
some more added fields. I would like to copy the data from the old table to
the new table which has more fields. Is there a way to do this? I tried a
few things but no luck.

Thanks.
Click to expand...

An Append Query is designed for precisely this purpose.

Create a Query based on your "old" table; select the fields you want
to copy, and if you have constant values or expressions that you want
to put into the newly added fields, put those expressions in vacant
Field cells in the query.

Using the Query Type icon, change the query to an Append query. Access
will ask what table you want the data appended into - select your new
table.

Run the query using the ! icon and it will copy all the data you've
specified!
 
Back
Top