Please help with Crosstab Query

[Endlessknight]

Greetings all,

I have a table in Access 2013 that look like this:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Child[/TD]
[TD]Pet1[/TD]
[TD]Pet2[/TD]
[TD]Pet3[/TD]
[TD]Pet4[/TD]
[/TR]
[TR]
[TD]Timmy[/TD]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[TD]Rabbit[/TD]
[/TR]
[TR]
[TD]Jimmy[/TD]
[TD]Dog[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bobby[/TD]
[TD]Cat[/TD]
[TD]Rabbit[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Todd[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













But what I would like is a table that looks like this

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Cat[/TD]
[TD]Dog[/TD]
[TD]Bird[/TD]
[TD]Rabbit[/TD]
[/TR]
[TR]
[TD]Timmy[/TD]
[TD]Timmy[/TD]
[TD]Timmy[/TD]
[TD]Timmy[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Jimmy[/TD]
[TD]Todd[/TD]
[TD]Bobby[/TD]
[/TR]
[TR]
[TD]Bobby[/TD]
[TD]Todd[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]









Is such a change possible?

Thanks in advance for any help/input offered.

 

[xenou]

Well, I don't think it can be done with a crosstab, since they rely on Aggregate functions and you aren't returning any thing that is being counted (or summed, or averaged).

 

[Endlessknight]

Well, I don't think it can be done with a crosstab, since they rely on Aggregate functions and you aren't returning any thing that is being counted (or summed, or averaged).

Any suggestions on where should I start?

 

[Micron]

As noted, what you show isn't a cross tab query output. I think the closest you can get is (supply your table/field names)

TRANSFORM Count(tblPetsXtab.Pet2) AS CountOfPet2
SELECT tblPetsXtab.Child, tblPetsXtab.Pet1, tblPetsXtab.Pet2, tblPetsXtab.Pet3, tblPetsXtab.Pet4
FROM tblPetsXtab
GROUP BY tblPetsXtab.Child, tblPetsXtab.Pet1, tblPetsXtab.Pet2, tblPetsXtab.Pet3, tblPetsXtab.Pet4
PIVOT tblPetsXtab.Pet1;

You can either hide the unwanted fields or use this query as the basis for a select query.

If that's what your table looks like, I'd say your data isn't properly normalized. If you had to add a field because someone got a fish, then I know it isn't.

 

[Endlessknight]

As noted, what you show isn't a cross tab query output. I think the closest you can get is (supply your table/field names)

TRANSFORM Count(tblPetsXtab.Pet2) AS CountOfPet2
SELECT tblPetsXtab.Child, tblPetsXtab.Pet1, tblPetsXtab.Pet2, tblPetsXtab.Pet3, tblPetsXtab.Pet4
FROM tblPetsXtab
GROUP BY tblPetsXtab.Child, tblPetsXtab.Pet1, tblPetsXtab.Pet2, tblPetsXtab.Pet3, tblPetsXtab.Pet4
PIVOT tblPetsXtab.Pet1;

You can either hide the unwanted fields or use this query as the basis for a select query.

If that's what your table looks like, I'd say your data isn't properly normalized. If you had to add a field because someone got a fish, then I know it isn't.

Thanks for the input. The data is what it is. I'm using Access as a data manipulation tool not as an actual DB so normalization isn't really an issues.

Do you think this manipulation would be easier in Excel?

 

[xenou]

What's the end goal? Just getting dog and Jimmy in one place or another doesn't add extra information so why the need to change things around? And like Micron said, when someone gets a fish it all probably goes haywire again.

 

[Endlessknight]

What's the end goal? Just getting dog and Jimmy in one place or another doesn't add extra information so why the need to change things around? And like Micron said, when someone gets a fish it all probably goes haywire again.

The end goal is to get from the first table to the second. No data will ever be added.

 

[xenou]

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.

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.

 

[Endlessknight]

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.

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.

It's mostly for visualization purposes. The table in question has only 25 records but 234 fields. The first field is a list of roles. The rest of the fields classes included in those roles.

Thank you so much for you help. I try and make the VBA code work

 

[xenou]

Fair enough. Data visualization is not Access' strong point, although it's reporting is good and probably under-appreciated.