split column into rows.

[rsampathy]

HI,

I want to split a column into rows.


CODE CLIENT_SRC_ID LINKED_TO_FACILITY
================================================== =


ADV-000002-2011 10046271 F1, F2
ADV-000002-2011 10069892 F1
ADV-000002-2011 10045788 F1

I want to split this into :-

CODE CLIENT_SRC_ID LINKED_TO_FACILITY
================================================== =
ADV-000002-2011 10046271 F1
ADV-000002-2011 10046271 F2
ADV-000002-2011 10069892 F1
ADV-000002-2011 10045788 F1

thank you.

[ssanfu]

I don't know for sure, but it looks like the data is:

CODE........................ ADV-000002-2011
CLIENT_SRC_ID.......... 10046271
LINKED_TO_FACILITY... F1, F2

Assuming that "LINKED_TO_FACILITY" will only have one comma in the field, this code will edit the record with "Fx, Fy", changing it to "Fx" and adding a record with "Fy".

Put the code in a standard module, change the line

TableName = "testtest"

to the name of your table, and execute the code. DO THIS ON A COPY OF YOUR DATABASE first. You have been warned!!!

Public Sub SplitLines()
  Dim r As DAO.Recordset
  Dim SQL As String
  Dim sSQL As String
  Dim tmp As String
  Dim TableName As String
  Dim strAryLines() As String  'array - zero based
  Dim x As Long, y As Long
  '----------------------------

  'change testtest  to your table name
  TableName = "testtest"

  sSQL = "SELECT CODE, CLIENT_SRC_ID, LINKED_TO_FACILITY"
  sSQL = sSQL & " FROM " & TableName & ";"

  x = 0
  y = 0
  Set r = CurrentDb.OpenRecordset(sSQL)

  If Not r.BOF And Not r.EOF Then
    r.MoveLast
    r.MoveFirst

    Do Until r.EOF
      x = x + 1  'count of lines checked
      If InStr(1, r.Fields("LINKED_TO_FACILITY"), ",") > 0 Then
        strAryLines = Split(r.Fields("LINKED_TO_FACILITY"), ",")
        r.Edit
        r.Fields("LINKED_TO_FACILITY") = strAryLines(0)
        r.Update

        SQL = "INSERT INTO Testtest(CODE, CLIENT_SRC_ID, LINKED_TO_FACILITY)"
        SQL = SQL & " VALUES ('" & r.Fields("Code")
        SQL = SQL & "', '" & r.Fields("CLIENT_SRC_ID")
        SQL = SQL & "', '" & Trim(strAryLines(1)) & "');"
        '        Debug.Print SQL
        CurrentDb.Execute SQL, dbFailOnError
        y = y + 1  'count of lines edited/added
      End If
      r.MoveNext
    Loop

  End If

  r.Close
  Set r = Nothing
  MsgBox "Done!!! " & x & " lines checked - " & y & " lined edited/added"

End Sub