Split string into Field values in a separate table.

I have two tables (TableA, TableB) which are joined in a one to many relationship. Table A contains a field (FieldA) with concatented strings eg.(10,15,20,25,30). I need to split the string in FieldA into separate values and place those values into the many side records of TableB. The example is:

FieldA.Value = 10,15,20,25,30

Split and Append or Edit Records to TableB:

FieldA.Value = 10
FieldA.Value = 15
FieldA.Value = 20
FieldA.Value = 25
FieldA.Value = 30

I believe I can use the Split Function to split the string into an array, but cannot figure out how to go from there. Thanks in advance.
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

RamanhpConnect With a Mentor Commented:

you write following code in msaccess code editor  to achieve desired result

//please udpate your table details below

Dim rsIn As DAO.Recordset
Dim rsOut As DAO.Recordset

Set rsIn=currentDB.OpenRecordset("Name of table with field to split")
Set rsOut=currentDB.OpenRecordset("Name of table to hold new records")

Do While Not rsIn.EOF
  astrNames=Split(rsIn![Name of field to split],",")
  For i = 0 To UBound(astrNames)
     rsOut!{Name of Field]=astrNames(i)
There is a possibility of writing a complex query to do it, but the easiest way will be to write a stored procedure. The other option is to write a small program in language of your choice (like java) and read, split and insert. Depends on your comfort level with languages, you can write pl/sql or java
I may suggest to integrate Excel into it.
1. Export the table into Excel
2. In Excel, do the Text to Column command in the Data Tools of the Data Tab
3. You may do the Copy and Paste Special -> Transpose
4. Finally, you can import it back into Access

See image attached.

skennellyAuthor Commented:
Thank you Ramanhp, I appreciate it very much. It worked great. Now I can rework the code to target specific records on my own, but your answer was outstanding, just as described. Again, thanks.
All Courses

From novice to tech pro — start learning today.