That's not an end goal - its an intermediate goal. Are you trying to count something? Are you trying to correlate something? In other words, in what way will this table be of use to you?
I'm concerned you haven't thought this through because it's very hard to see how the second table is any more valuable than the first.
For kicks I have solved this problem by creating pairs and then outputting the result, but I suspect you will be less than pleased with that solution, even though it basically gets you what you want
The results here is a CSV table that could be easily transformed into an Access table if you need that.
Code:
Sub foo()
Dim rs As DAO.Recordset
Dim dicPets As Scripting.Dictionary
Dim i As Long
Dim nextChild As String
Dim nextPet As String
Dim Seq As Long
Dim vKey
Dim s As String
Set dicPets = CreateObject("Scripting.Dictionary")
Set rs = CurrentDb.OpenRecordset("Table5")
'Create child-pet pairs
If Not rs.EOF Then
Do While Not rs.EOF
If Not Nz(rs.Fields(0).Value, "") = "" Then
nextChild = rs.Fields(0).Value
For i = 1 To rs.Fields.Count - 1
If Not Nz(rs.Fields(i).Value, "") = "" Then
Seq = Seq + 1
nextPet = rs.Fields(i).Value
If dicPets.Exists(nextPet) Then
dicPets.Item(nextPet).Add nextChild, "K" & CStr(Seq)
Else
dicPets.Add nextPet, New VBA.Collection
dicPets.Item(nextPet).Add nextChild, "K" & CStr(Seq)
End If
End If
Next i
End If
rs.MoveNext
Loop
End If
rs.Close
Set rs = Nothing
'Print Column Headers (pets)
s = ""
For Each vKey In dicPets.Keys
s = s & vKey & ","
Next vKey
Debug.Print Left(s, Len(s) - 1)
'Print Column rows (children)
Do
s = ""
For Each vKey In dicPets.Keys
If dicPets(vKey).Count > 0 Then
s = s & dicPets(vKey)(1) & ","
dicPets(vKey).Remove (1)
Else
s = s & ","
End If
Next vKey
Debug.Print Left(s, Len(s) - 1)
If Len(Replace(s, ",", "")) = 0 Then
Exit Do
End If
Loop
End Sub
Output:
Cat,Dog,Bird,Rabbit
Timmy,Timmy,Timmy,Timmy
Jack,Jimmy,Todd,Bobby
Bobby,Jack,,
,Todd,,
,,,
Note that I'm not very optimistic about find a SQL based solution since this is basically non-relational (unstructured) data. There aren't any keys here and not only is it not normalized it really can't be normalized (the only way you could would be to create a table of children, a table of pets, and then have a linking table between them ... which wouldn't even make this any easier in terms of the output you want to show. But who knows I could be wrong. Sometimes Micron finds some very clever SQL.
