Updating a field in one table with the contents of a field in anot

[Guest]

I am trying to update a field in one table with the contents of a field in
another table.

My tables are:

Product
-Prod_PK -- Autonumber
-Prod_Proj_Fk -- Number
-Prod_Data_Fk -- Number
-Prod_PeerReview -- Yes/No
-Prod_Pub_Year -- Number
-Prod_Title -- Text
-Prod_Source_Cit -- Memo
-Prod_URL -- Text
-Prod_Journal -- Number

Journal
-Journal_PK
-Journal_Name

The tables had been linked Journal_PK --> Prod_Journal so that the UID for
the Journal was supposed to be put into the Prod_Journal field. Somehow the
journal names from the Journal_Name field ended up in the Prod_Journal field
even though that field datatype is Number. I want to correct the problem,
and fix the linking as well.

I thought the best way to do this would be to update the
Product.Prod_Journal field with the contents of the Journal.Journal_Pk field
and match based on Journal.Journal_Name = Product.Prod_Journal. Then I
figured that you couldn't match based on a field and then change that same
field's content. Anyway, there are nearly 4000 products and 300 Journals and
Iwould REALLY like to not have to fix this by hand. I have searched MS site
and google for a Update or Insert Into solution but nothing seems to work in
Access.

Please Help!!

Donovan

 

[Guest]

Access support both Updates (update query) and Insert Into (Make-table
query). If you're familiar with SQL, as it sounds like you are, you can write
sql syntax directky by opening a query in SQL View.

I think you're going to have to make a new temporary table, a copy of the
journal table. Use this to create the association.

Barry