New table by grouping fields

deb

Active Member
Joined
Feb 1, 2003
Messages
400
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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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.

Code:
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
 
Last edited:
Upvote 0
Just had a thought - you would need to watch out for commas that don't act as a delimiter - i.e. in a comment.
 
Upvote 0
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.



Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top