[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 940
  • Last Modified:

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:

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

Split and Append or Edit Records to TableB:

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.
0
skennelly
Asked:
skennelly
1 Solution
 
brain_boxCommented:
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
0
 
MINDSUPERBCommented:
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.

Sincerely,
Ed
text-to-column.jpg
0
 
RamanhpCommented:

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.AddNew
     rsOut!{Name of Field]=astrNames(i)
     rsOut.Update
  Next
  rsIn.MoveNext
Loop
 
0
 
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.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now