Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Split string into Field values in a separate table.

Posted on 2011-03-11
4
Medium Priority
?
938 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

618 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