Forum Discussion
Change data from a row to a column
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.
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