Using Crosstab Queries for Data Lists?

[JoeM]

I openly admit, I seldom use Crosstab Queries, and the few times that I have, I am usually performing some mathematical computation on the data.


However, what if I just want to list data going across? It seems like it should be some simple, but it is stumping me. I have tried Googling it, but haven't found it (everything I find is more complex).

Here is a really simple example of a table with two bits of data:

Parent     Child
Adam       Ben
Adam       Chris
Adam       Donna
Mary       Bill
Ron        Steve
Ron        Tara

And I would like the Crosstab Query to return results like this:

Parent    Child1    Child2    Child3
Adam      Ben       Chris     Donna
Mary      Bill
Ron       Steve     Tara

Seems like it should be so simple, yet it eludes me...

[June7]

Unless you have a field in table that numbers the children for each parent (that numbering field would become the column header), there is no easy way to accomplish this. VBA code options:

1. write data to a temp table in the non-normalized structure

2. http://allenbrowne.com/func-concat.html

[Dal Jeanis]

You can do it even without a "number" field, as long as the records have unique keys. You'd create a subquery against the same table that assigned each child an "order" based on their key - lowest is 1, next is 2, and so on.

That would look roughly like one of these:

MyTable
MyKey   PK
Parent  Text
Child   Text
 
SELECT 
   T1.MyKey, 
   T1.Parent, 
   T1.Child,
   (SELECT Count(*) FROM MyTable AS T2
    WHERE T1.Parent = T2.Parent 
      AND T1.MyKey <= T2.MyKey) AS Childnumber
FROM MyTable AS T1;  

SELECT 
   T1.MyKey, 
   T1.Parent, 
   T1.Child, 
   Count(T2.Child) AS ChildNumber
FROM 
   MyTable AS T1, 
   MyTable AS T2  
WHERE T1.Parent = T2.Parent 
  AND T1.MyKey <= T2.MyKey
GROUP BY T1.MyKey, T1.Parent, T1.Child;

The second syntax looks better at the moment, but the first should work as well. Both are aircode, so they're worth precisely what you paid for them.

I don't think a crosstab query is exactly what you need, though. You're not adding up the kid's names, so crosstab just isn't the right concept.

[June7]

Very nice Dal. I should have presented that as another option.

Joe, if table doesn't currently have a unique ID field, just add an autonumber type field to the table. If you need more info about subqueries, review: http://allenbrowne.com/subquery-01.html

A crosstab could be used if there was a group numbering field because the aggregate function used would be First.

[JoeM]

Thanks for the tips, guys. I am trying to help someone else do this, and wasn't sure how to accomplish it. In the back of my mind, I thought not having a numbering field could be problematic.
Anyway, its nice to know I wasn't overlooking anything obvious.

[JoeM]

Dal,

I tested out your solutions, and they work well with preparing the data for a Crosstab Query, with one small caveat.
In the case in which order matters (which is not always necessarily the case), the code as written lists the Children in reverse order.
However, I was able to remedy that easily by changing these rows:

AND T1.MyKey <= T2.MyKey

to this:

AND T1.MyKey >= T2.MyKey

To be fair, I never mentioned whether or not order matters (and I have to check with the user to see if it does). But in any event, your code gave me what I need.

Thanks!

[Dal Jeanis]

It's not a crosstab, but it turned out to be not that hard, after sleeping on it.

There's a standard method for spreading data sideways using IIF for each horizontal slot. It's normally used with SUM and GROUP BY, but for text data you could use subqueries and FIRST to make it happen.

Here's the table structure:

tblParentChild
pcPK      PK
pcParent  Text
pcChild   Text

Here's the query, using the new field names, to assign child number:

Query1:
SELECT 
   T1.pcPK, 
   T1.pcParent, 
   T1.pcChild, 
   Count(T2.pcChild) AS pcChildNo
FROM 
   tblParentChild AS T1, 
   tblParentChild AS T2  
WHERE T1.pcParent = T2.pcParent 
  AND T1.pcPK >= T2.pcPK
GROUP BY T1.pcPK, T1.pcParent, T1.pcChild;

Results of Query3:
pcPK  pcParent pcChild
 1      Adam     Ben
 2      Adam     Chris
 3      Adam     Donna
 4      Mary     Bill
 5      Ron      Steve
 6      Ron      Tara

Here's a second query just to get the total number of children for any particular parent, because it will simplify the final combination query:

Query2:
SELECT 
   T3.pcParent, 
   Count(T3.pcChild) AS pcChildCt
FROM 
   tblParentChild AS T3  
GROUP BY T3.pcParent;

Results of Query2:
pcParent pcChildCt
Adam          3   
Mary          1   
Ron           2

And here's the query you were looking for, using the above two queries as components:

Query3:
SELECT
   Q2.pcParent,   
   Q2.pcChildCt,
   (SELECT First(Q11.pcChild) 
   FROM Query1 AS Q11 
   WHERE Q11.pcParent = Q2.pcParent
   AND Q11.pcChildNo = 1)  AS pcChild1,
   IIF(pcChildCt>1,(SELECT First(Q12.pcChild) 
   FROM Query1 AS Q12 
   WHERE Q12.pcParent = Q2.pcParent
   AND Q12.pcChildNo = 2),"") AS pcChild2,
   IIF(pcChildCt>2,(SELECT First(Q13.pcChild) 
   FROM Query1 AS Q13 
   WHERE Q13.pcParent = Q2.pcParent
   AND Q13.pcChildNo = 3),"") AS pcChild3,
   IIF(pcChildCt>3,(SELECT First(Q14.pcChild) 
   FROM Query1 AS Q14 
   WHERE Q14.pcParent = Q2.pcParent
   AND Q14.pcChildNo = 4),"") AS pcChild4
FROM 
   Query2 AS Q2;

Results of Query3:
pcParent pcChildCt  pcChild1  pcChild2  pcChild3  pcChild4
Adam          3       Ben       Chris     Donna
Mary          1       Bill
Ron           2       Steve     Tara

FYI, you could go as many slots wide as you felt was likely to happen with your data. If you needed, you could use a VBA routine to find DMAX of Query2.pcChildCt and build Query3 using VBA to accommodate that many children.

Full Disclosure: The SQL copied in above from these queries was tested as coded, although the results shown above were typed in. Any errors in the table values and results are typos, not a problem with the code.

[Dal Jeanis]

JoeM - ROFL. I found the same order issue so I reversed the sign when testing the above sample code.

[JoeM]

Thanks Dal.

I actually came up with a similar solution that worked.

1. I created a "Counter" field to the table, and sorted the Table on "Parent" and used VBA to loop through the Recordset and assign a Counter value to each record (starting over at 1 with each new "parent")
2. Created a query with calculated fields, i.e.

Child1: IIF([Counter]=1,[Child],"")

3. Repeated step 2, adding more calculated fields, for maximum number of children
4. Changed the query to an Aggregate query
5. Changed the Totals Row value on the Calculated Fields to "Max"

That seemed to return exactly what I wanted!

[Dal Jeanis]

Generally speaking, you shouldn't add calculated fields to the table itself. You'd put those in a temporary table that you can delete and recreate it at need.

That being said, as long as you know what you did, and you know how it worked, then you're good to go. Take care.

[JoeM]

Generally speaking, you shouldn't add calculated fields to the table itself. You'd put those in a temporary table that you can delete and recreate it at need.

Agreed. For this purpose, it works fine because basically it is a one-time process on a set of data (so no data is altered, edited, etc). If we did it again, it would be on a new data set, so we would delete all current data and start over.