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:
Code:
tblParentChild
pcPK PK
pcParent Text
pcChild Text
Here's the query, using the new field names, to assign child number:
Code:
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:
Code:
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:
Code:
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.