?
Solved

Split string into Field values in a separate table.

Posted on 2011-03-11
4
Medium Priority
?
927 Views
Last Modified: 2012-06-27
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
Comment
Question by:skennelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 2

Expert Comment

by:brain_box
ID: 35114020
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
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 35114500
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
 
LVL 9

Accepted Solution

by:
Ramanhp earned 2000 total points
ID: 35114600

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
 

Author Closing Comment

by:skennelly
ID: 35116167
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question