How do I convert mixed number and text data points within a column into only the number coding

[BlueSkadoo]

I have a mixed database of paper entry and Qualtrics entry data. The paper entry forms converted number values for sessions (1, 2, 3, 4, etc) into text representations (Session_1, Session_2, Session_3, Session_4), and the newer data recorded the sessions in numerical form only. The client wants just the numerical form of session identifier. I have a table that equates the Session_Name and Session_Code. How do I use this table to convert all of the points in several merged tables of data to the numerical code? The Session naming code isn't simple unfortunately so I don't think I can use just the VAL function. (Week_1_Followup is 9 for example).
The tables I need to update with only a numerical value are often deleted and recreated with new collected data using several select and add queries, however the names of all merged tables always stay the same. Thank you to anyone who can give me a hand, I'd rather not have to edit the values in an Excel export.

[June7]

If you want to actually delete session name from the data (which actually makes sense), create a number field for this number value. Build a query that joins data and session tables on session name, change it to an UPDATE action to populate the new field, delete the old name field.

Now when you do want the name value, build query that joins tables on code number to retrieve associated information.

Provide data for us to work with. Can attach db to post.

[Gicu]

You can use a lookup table to do the "translation" for you; you would have two fields, a short text field for the original values and a numeric one for the final value.
So for Week_1_Followup you would have two entries, one with that in the original and 9 in final and another with 9 in both fields. Once you build this table it is very easy to get your numeric values in any query, form or report as needed as you would simply join the original tables on the original value value and bring the numeric value from the new table.

Cheers,