New table by grouping fields

[deb]

Access 365
Need a table that looks like this....
Table has several questions, answers and comments in each record.
RecordID123, Question1, Answer1, Comment1, Question2, Answer2, Comment2 and so on.....

To look like this...
Multiple same ID with each set of question, answer and comment
RecordID123, Question1, Answer1, Comment1
RecordID123, Question2, Answer2, Comment2
RecordID123, Question3, Answer3, Comment3
And so on with more in RecordID123

How can I do this.
I inherited the crazy data from Survey Monkey.

 

[stumac]

It can be done with VBA - in the code below I have highlighted the fields you would change to suit your DB. In the example the data would be in a table called MyTable within a field called MyField and would be split out into a table called MyNewTable with fields called RecordID, Question, Answer, Comment.

Sub SplitData()
Dim Rs1 As Recordset 'Current Data
Dim Rs2 As Recordset 'Split out data
Dim Rec As String 'current record
Dim x As Integer 'Counter
Set Rs1 = CurrentDb.OpenRecordset("[COLOR=#ff0000]myTable[/COLOR]")
Set Rs2 = CurrentDb.OpenRecordset("[COLOR=#ff0000]MyNewTable[/COLOR]")
Rs1.MoveFirst
    Do While Not Rs1.EOF
        Rec = Rs1![COLOR=#ff0000]myfield[/COLOR]
        For x = 1 To UBound(Split(Rec, ",")) Step 3
            Rs2.AddNew
            Rs2![COLOR=#ff0000]RecordID[/COLOR] = Split(Rec, ",")(0)
            Rs2![COLOR=#ff0000]Question[/COLOR] = Split(Rec, ",")(x)
            Rs2![COLOR=#ff0000]Answer [/COLOR]= Split(Rec, ",")(x + 1)
            Rs2![COLOR=#ff0000]Comment [/COLOR]= Split(Rec, ",")(x + 2)
            Rs2.Update
        Next x
        Rs1.MoveNext
    Loop
Set Rs1 = Nothing
Set Rs2 = Nothing
MsgBox "Complete"
End Sub

 

[stumac]

Just had a thought - you would need to watch out for commas that don't act as a delimiter - i.e. in a comment.

 

[stumac]

Realised that your data is probably in different fields (not one field separated by commas), the code above wont work as is for that. If that's the case and the fields are in the correct order (as shown above) then something like the following would work.

Sub SplitData()
Dim Rs1 As Recordset 'Current Data
Dim Rs2 As Recordset 'Split out data
Dim x As Double 'Counter
Set Rs1 = CurrentDb.OpenRecordset("[COLOR=#ff0000]myTable[/COLOR]")
Set Rs2 = CurrentDb.OpenRecordset("[COLOR=#ff0000]MyNewTable[/COLOR]")
Rs1.MoveFirst
    Do While Not Rs1.EOF
        For x = 1 To Rs1.Fields.Count - 1 Step 3
            Rs2.AddNew
            Rs2![COLOR=#ff0000]RecordID[/COLOR] = Rs1.Fields(0)
            Rs2![COLOR=#ff0000]Question[/COLOR] = Rs1.Fields(x)
            Rs2![COLOR=#ff0000]Answer[/COLOR] = Rs1.Fields(x + 1)
            Rs2![COLOR=#ff0000]Comment [/COLOR]= Rs1.Fields(x + 2)
            Rs2.Update
        Next x
        Rs1.MoveNext
    Loop
Set Rs1 = Nothing
Set Rs2 = Nothing
MsgBox "Complete"
End Sub

 

[deb]

Thank you, I will try this. It looks great