Replace text with related number



I have an access database table ([Table1]) in which I have many fields
related to fields from other tables [Table2], [Table3]] etc.
The fields [Field1-1] from [Table1] is related to [field2-2] from [Table2]
in one-many relationship.
Currently the field [Field1-1] from [Table1] contains the full text from
[field2-2] of [Table2]. whereas I want to store the index values (from
autonumber field) from [Field2-1] from [Table2] in the [Table1] instead of
the full text value from [Table2].
How can I replace the full text value in [Table1] with index values from
[Table2] without having to retype all the values.

Kindly advise.




Backup database. BACKUP DATABASE.
Make a copy of Table1. Add a field to Table1 that is datatype number - long
Create a new query - open in design view.
Place both tables in the area above the grid.
Click on field [Field1-1] from [Table1] and drag to [field2-2] of [Table2].
This creates a connecting line between the two fields.
Drag the autonumber field of Table2 to the Field row of the grid. Drag the
new field of Table1 to the Field row of the grid.
On the icon bar click Update Query.
Double click the autonumber field of Table2 that is in the Field row. Copy.
Click into the Update To row of the grid under the new field of Table1.
Enter a left bracket -- [
Paste autonumber field of Table2 copied before and type a right bracket. It
will then look like this --
Run the query.
Clock on the Select Query icon. Run the query and see if the new field of
Table1 was updated.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question