Forum Discussion

ygoshy's avatar
ygoshy
Copper Contributor
Nov 09, 2020

Change data from a row to a column

I have a column in access and/or excel as follows:

 

ROW PCFN

1.      123456, 456789, 452123, 741852 or can remove commas between each number.

2.      987456, 521364, 796321, etc.

I am trying to convert this column to:

ROW PCFN

1.       123456

2.        456789

3.       452123

4.       741852

5.       987456

6.       521364

7.       ETC.

 

Can anyone help. I originally received this data in a excel worksheet with commas between each number. I can take out the commas if needed. I have also copied and pasted the column in a table in access.

 

Thanks Tom

 

access

3 Replies

  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Nov 10, 2020

    ygoshy 
    I will try to come up with a sample function to do this, but the approach is going to be based on the Split() function to place each of the values in a row into an array. Take a few minutes, please, to read about the Split() function. I'll see how quickly I can do it, but it may be a little while.

    • George_Hepworth's avatar
      George_Hepworth
      Silver Contributor
      Nov 10, 2020

      George_Hepworth

      I created two tables, one for the source data and one for the output data. They are identical with two fields: Row and PCFN.

       

      This function splits the comma delimited source rows into an array then then extracts the items from the array into the output table, one row per item.

       

       

      Public Sub RowToColumn()

      On Error GoTo errHandler
      Dim rst As DAO.Recordset
      Dim db As DAO.Database
      Dim arySourceRow() As String
      Dim lngRow As Long
      Dim i As Integer

      lngRow = 1
      Set db = CurrentDb
      Set rst = db.OpenRecordset(Name:="SELECT Row, PCFN FROM SampleTable", Type:=dbOpenDynaset)
      With rst
      Do Until .EOF
      arySourceRow = Split(!PCFN, ",")
      For i = 0 To UBound(arySourceRow())
      db.Execute "INSERT INTO FinalData (Row, PCFN) SELECT " & lngRow & ", " & arySourceRow(i), dbFailOnError
      lngRow = lngRow + 1
      Next i
      .MoveNext
      Loop
      End With


      Cleanup:

      On Error Resume Next
      Set db = Nothing
      Set rst = Nothing

      exitProc:

      Exit Sub

      errHandler:

      MsgBox Prompt:="Error " & Err.Number & " (" & Err.Description & ") in procedure RowToColumn, line " & Erl & ".", _
      Buttons:=vbCritical + vbOKOnly, _
      Title:="Something Bad Happened"
      Resume Cleanup
      Resume

      End Sub

       

       

Resources