Nov 09 2020 03:16 PM
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
Nov 09 2020 04:45 PM
@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.
Nov 09 2020 05:24 PM
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
Nov 10 2020 08:43 AM
@ygoshy I created a sample relational database application to illustrate this approach. It's on my public OneDrive.