Solved

Split string into Field values in a separate table.

Posted on 2011-03-11
4
888 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
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 500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now