Replace Delimited Field with New Values from Table

[elfmajesty]

Good afternoon, all. I am struggling with "find and replace" problem that I thought I could do in Access.

I have a table of data of two fields where one field has data delimited by a semicolon. I have another table of "cross-ref" numbers. I need to replace the original delimited data with the cross-ref numbers.

TABLE001
Field1 - ID Field2 - DelimitedData

TEST001 John;Susan;Mary
TEST002 Susan
TEST003 John;Mary
TEST004 John;Susan


TABLE002-CrossRef
Field001 Field003

John 100
Susan 200
Mary 300

I need my result to be
TEST001 100;200;300
TEST002 200
TEST003 100;300
TEST004 100;200

Any way I can do this using Access with the two tables I have?

Thank you in advance,
Elf

 

[Micron]

I can only see a solution that involves code because there is no link between an element of the delimited data (which is in effect, an array) and the corresponding field in the other table. I'm thinking the code would have to assign the field data to an array (John;Susan;Mary) then split it and step through the elements. Then run a sql statement (or simply do a DLookup) on the other table by using each array element as a key and retrieve the corresponding value from field 003. Once you have that, based on your post I'd guess that you'd run an update query for each array element. I presume you realiize that the target table has to have a value in order to perform an update on it. Otherwise, you'd probably have to update a DAO recordset. Hope that makes sense.

 

[elfmajesty]

Thanks, Micron. I appreciate the reply. Yes, pretty complicated. I'm looking into attempting to write formula in Excel with VLookup and either Substitute or Replace in conjunction. Will try to update here if I come up with anything brilliant.