split a text field into multiple columns in Access 2007

I have a text field formatted as such "001, 002, 003" There could by any number of comma separated values in the field. I would like to parse this text field apart into N columns (anywhere from 1 to N). I know this has been covered before but i couldn't quite figure it out based on other posts. Thanks.

The field text is called [Item Numbers], and the new fields should be called [Item1], [Item2], [Item3]. etc...

Yaniv SchiffDirector of Digital Forensics Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure what you mean by "columns". Generally, you'd insert each of those items into a Row in a table, not in columns across a single Row.

You can use the Split function to do this:

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = Currentdb.OpenRecordset("SELECT * FROM YourTAble")
arr = Split(rst("YourField"), ",")

For i = 0 to Ubound(arr)
  '/do something here
Next i
0
Yaniv SchiffDirector of Digital Forensics Author Commented:
that's pretty much what i have so far, it's the "do something here" part that i'm getting stuck at. What i want it to do is create a new column for each item number and insert the item number into the field. Attached is the code i have so far which creates the columns, i'm not sure how to insert the item number value into each field as it works it's way through the table though. Does that make sense?
Sub test()
DoCmd.SetWarnings (WarningsOff)

Dim s As String
Dim a() As String
Dim i As Integer

Dim strFieldName As String

s = "001, 002, 003, 004, 005"


a = split(s, ",")

For i = 0 To UBound(a, 1)
strFieldName = "Item" & i

CreateField "Slips", strFieldName

Debug.Print a(i)

Next i


End Sub


Function CreateField( _
      ByVal strTableName As String, _
      ByVal strFieldName As String) _
      As Boolean

   'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
   'Set references by Clicking Tools and Then References in the Code View window
   'Creates a Text field, other data types listed
   '
   ' strTableName : Name of table in which to create the field
   '
   ' strFieldName : Name of the new field to add to table
   ' Returns True on success, false otherwise
   '
   'USAGE: CreateField "TABLENAME", "FIELDNAME"

   On Error GoTo errhandler

   Dim Db As DAO.Database
   Dim fld As DAO.field
   Dim tdf As DAO.TableDef

   Set Db = Application.CurrentDb
   Set tdf = Db.TableDefs(strTableName)

   ' First create a field with data type = Text
   Set fld = tdf.CreateField(strFieldName, dbText)

   'A few Alternate datatypes: for DAO - Note: The listed Complex data types require
         ' Access 2007 or higher
   'Long = dbLong or dbComplexLong
   'Single = dbSingle or dbComplexSingle
   'Double = dbDouble or dbComplexDouble
   'Integer = dbInteger
   'Decimal = dbDecimal or dbComplexDecimal
   'Text = dbText or dbComplexText
   'Memo = dbMemo
   'Currency = dbCurrency
   'Yes/No = dbBoolean
   'Date = dbDate

   ' Appending the field
   With tdf.Fields
      .Append fld
      .Refresh
   End With
   CreateField = True

ExitHere:
   Set fld = Nothing
   Set tdf = Nothing
   Set Db = Nothing

   Exit Function

errhandler:
   CreateField = False

   With Err
 
   End With

   Resume ExitHere

End Function

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Creating a column for each field does NOT make sense. As I stated earlier, you really should be putting these in Rows of your table.

What exactly does the data represent?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Yaniv SchiffDirector of Digital Forensics Author Commented:
Each row represents a work ticket. Each work ticket is associated with multiple Items (as indicated by the [Item Numbers] column). Each item number is 3 digits (ie. 001, 002) separated by a comma. I would like to take each item number and store it in a separate column for each work ticket. The first screen shot(singlecolumn), shows how the data is currently stored. The second, (multicolumn) shows how i would like it.

SingleItemNumberColumn.jpg
MultipleItemNumberColumn.jpg
0
Patrick MatthewsCommented:
Forensicon,

I urge you to re-read LSMConsulting's comment http:#a31405421, and to take his advice to heart.  Putting this into separate columns is a stupendously bad idea.  All the principles of proper database design would have you place these items into separate rows, and not separate columns.

Patrick
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In a relational database, you would have a WorkTicket table, and you'd also have a separate table to handle those "items", whatever they may be. You would "relate" that "item" table back to the WorkTicket table by storing the WorkTicket.ID value in the Item table row.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Yaniv SchiffDirector of Digital Forensics Author Commented:
I agree with both of you that creating multiple columns is a bad idea. The problem i'm running into though is that all this information is coming out of sharepoint and i'm trying to make do with what i have. So i understand, you guys are suggesting that instead of creating multiple columns to store the item numbers, that i create a new row for each item number. EG.

ItemNumber                       WorkTicketNumber
001, 002, 003                         1234

Would result in:

ItemNumber                       WorkTicketNumber
001                                     1234
002                                     1234
003                                     1234


Is that correct?
0
Patrick MatthewsCommented:
That's the idea :)
0
Yaniv SchiffDirector of Digital Forensics Author Commented:
i'm beginning to get it., Attached is the code i have so far which works well to loop through the whole table [Slips] and parse out each item number for each row. My thought is to create another table called [ItemNumberWorkTickets] which will have 2 columns, "ItemNumber" and "SP_WorkTicketID". As i loop through my Slips table i want to pull out each ItemNumber and WorkTicketID and insert it into the [ItemNumberWorkTickets] table. This way i can run join queries later as you guys suggested. What would be the sql statement to do this? Thanks for the good advice guys.
Sub split1()

Dim strFieldName As String
Dim sql As String

Dim rst As DAO.Recordset
Dim arr() As String
Dim i As Integer

Set rst = CurrentDb.OpenRecordset("SELECT Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips where [Item Numbers] is not null;")

Do While Not rst.EOF


arr = split(rst("[Item Numbers]"), ",")

    For i = 0 To UBound(arr)
    Debug.Print arr(i)

sql = "INSERT INTO ItemNumberWorkTickets ( ItemNumber, SP_WorkTicketID )SELECT " & arr(i) & " AS Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips;"
DoCmd.RunSQL sql
  
  

    Next i
rst.MoveNext
Loop

End Sub

Open in new window

0
Yaniv SchiffDirector of Digital Forensics Author Commented:
any thoughts?
0
Yaniv SchiffDirector of Digital Forensics Author Commented:
i figured it out using the code provided by lsm and other posts. i included it below for reference. Thanks to both experts.
Public Function BreakToWords()
Dim rsOrig As DAO.Recordset
Dim rsNew As DAO.Recordset
Dim vArr As Variant 'array to hold the split phrase
Dim i As Integer 'counter

Set rsOrig = CurrentDb.OpenRecordset("SELECT Slips.[Item Numbers], Slips.SP_WorkTicketID FROM Slips where [Item Numbers] is not null;")
Set rsNew = CurrentDb.OpenRecordset("ItemNumberWorkTickets")

If rsOrig.RecordCount <> 0 Then

'loop the rsOrig records
rsOrig.MoveFirst
While Not rsOrig.EOF
'split the phrase on a space delimiter
vArr = split(rsOrig("[Item Numbers]"), ",")

'loop the array (words) and add to rsNew
For i = 0 To UBound(vArr)
With rsNew
Debug.Print vArr(i)
.AddNew
.Fields("SP_WorkTicketID") = rsOrig("SP_WorkTicketID")
.Fields("[ItemNumber]") = Trim(vArr(i))
.Update
End With
Next

rsOrig.MoveNext
Wend

End If

rsOrig.Close
rsNew.Close
Set rsOrig = Nothing
Set rsNew = Nothing

MsgBox "Complete"


End Function

Open in new window

0
Yaniv SchiffDirector of Digital Forensics Author Commented:
based on LSM's suggestions i was able to use the code provided and other code found on EE.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.