Copy field data from one table to another

[anthrock]

I have 2 tables in the same database file. I'm trying to write a query to copy data from a table 1 column into an empty column on table 2. Both tables already exist and have data in them, but table 2 has an empty column for this copied data.

I want to copy based on a WHERE clause, where the primary keys equal each other.

I've tried to use INTO but it just creates a new table. I do not want to create a new table; I want to use an existing table. Any suggestions?

Thanks!

[SDas]

Access isn't my primary area of knowledge so I don't know if this syntax will work, but you can try:

UPDATE table-name A
SET col-name =
(SELECT col-name
FROM table-name B
WHERE A.pri-key-col = B.pri-key-col

NOTE if more than 1 key makes up the Primary key add a line like this for every additional column otherwise leave it out:

AND A.pri-key-col2 = B.pir-key-col2
)

[anthrock]

Thank you for the reply. I've tried the code and am getting the following error: "Operation must use an update-able query". We were looking at using a Dlookup instead of a SELECT but we're not sure if it will work any different and are unsure how to phrase it.

Again, any help is appreciated. Thanks for the reply!

[SDas]

What version of Access are you using?

[anthrock]

Access 2K3

[SDas]

I can't find SQL syntax in Access very easily. However there are instructions for doing this in the Queries Design View:

Open Access.
Press F1 for Help.
In the Search for box type UPATE.
Click on the topic 'Create an Update query'
Click on 'Create an Update Query (MDB)'
Under "In this topic...", click on 'Update the data in one table by using data from another table'.

This should have the information you need to run the update from the Design View. You can switch to SQL view after it is created and see the SQL.

[anthrock]

Thank you so much, we didn't realize there were special query design views for different types of queries (make table, append, delete, etc).

Figured out the solution, it was very straightforward. The update query design view was a huge help.