Question how to handle multiple find/replace actions

[madwoman]

I have a table containing courses data that has been exported from a website application (MySQL) that I need to use in an local Access database application. One of the fields that is in the website table includes ID values from another related table on the website and I need to update these ID values and convert them to text values so that I can manipulate the Access data locally.

Eg. SQL courses table values exported and imported into Access
id course_title disciplines
1 microscope maintenance 1, 3, 13, 15
2 spectometer maintenance 1, 4, 5, 15

The integers in the disciplines field need to be replaced with the following values:
id discipline
1 biochemical
3 microbiol
4 chemistry
5 histology
13 cells
15 safety

I need to automate this process so that each time a new version of the courses table is exported and imported into Access I can run a process that converts all the discipline integer value to their relevant text value.

I can do this manually for each discipline value using the find/replace action but that will be somewhat impractical as the number of discipline values increases. How can I automate the process - I assumed this could be done through a macro but I don't know how I can set that up. I tried using an update query but that doesn't seem to allow replacement of partial string. Could you please advise how I can do this.

 

[Brianwarnock]

I wouldn't replace them, but would maintain a disciplines table and look up the text when required for display.

However, I'm no DB design guru, but I do wonder if any of the experts would suggest an alteration to your design, yes I know this is not yours, because I think that analysis of the data, even a simple report on a discipline will not be easy.
One question, is the number of disciplines per course fixed?

Brian