Query to copy Records from one table to another.

[Mike G.]

I want to copy several fields of records form one table to another. However,
each table has an autogenerated field that is the primary key in each. I
would like to use another field that's common to both tables as the primary
key for this record transfer. I've tried an append query and an update query
with not much luck. Does anyone know how to do this? Any and all advise will
be greatly appreciated.
thanks

 

[Dale Fye]

Mike,

Let me make sure I understand.

Are you trying to insert records in the second table, or update fields in
records that already exist? It sounds like you are trying to update fields
that already exist..

You should be able to write your SQL something like:

Update tbl1
INNER JOIN tbl2
on tbl1.CommonField = tbl2.CommonField
SET tbl1.SomeField = tbl2.SomeField, tbl1.AnotherField = tbl2.AnotherField

In some instances, the fields you join will cause this query to not be
updateable (see http://www.allenbrowne.com/ser-61.html for a list of reasons
that this might be the case). If your query is not updateable, I generally
ensure that the query contains the PK from table1. Then I create another
query that joins table1 to my query. If that doesn't work, I'll write the
results of query1 to a temp table and link it to table1..

Dale