how to update fields in a table

[Guest]

We have an Access database that contains some 4500 records. In the database
there is a table named products. In the products table we have numerous
columns. In particular, we have columns for the item number, product name,
and eight fields pertaining to the related product information. We recently
went through and updated all the product names, which are located in the Name
field. What we'd like to do is update the product names in the related
product fields (Related1Name, Related2Name, Related3Name and Related4Name).
Here's a list of the fields in question:
SKU
Name
Related1
Related1Name
Related2
Related2Name
Related3
Related3Name
Related4
Related4Name
What I'd like to see happen is to run maybe an update query that would match
the item number in the Related1 field with the item number in the SKU field
and then update the product name in the Related1Name field with the data from
the Name field. Of course, we'd want to update the records in the Related2,
Related3, and Related4 fields as well.

How would I accomplish this? Thanks!

 

[Douglas J. Steele]

I'd strong suggest rethinking your design. Repeating groups such as you have
are a real pain to work with (not to mention that they violate relational
database design theory).

You should have 2 tables, something like:

Table1
---------
SKU (PK)
Name

Table2
---------
SKU (Compound PK)
RelationNumber (Compound PK)
Relation
RelationName

Any record that has 4 related records would instead have 4 rows in table 2.