Solution: Concatenate rows from same column

[Sharknwfk]

"Sharknwfk" <sh*******@co.nz.uk> wrote in message
news:41******@clear.net.nz...

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:aq********************************@4ax.com...

On Sun, 12 Dec 2004 14:16:57 +1300, "Sharknwfk" <sh*******@co.nz.uk>
wrote:

"Chuck Grimsby" <c.*******@worldnet.att.net.invalid> wrote in message
news:ub********************************@4ax.com.. .
> Do a CrossTab query. The CrossTab Query Wizard will do all the work
> for you.

Reposted for future users.
Okay, here's how it works.

2 Crosstabs

Female:

TRANSFORM First([Children Query].Age) AS FirstOfAge
SELECT [Children Query].ClientID
FROM [Children Query]
GROUP BY [Children Query].ClientID
PIVOT [Children Query].Expr1 In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,
29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45 ,46,47,48,49,50,51,52,53,5
4,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70, 71,72,73,74,75,76,77,78,79
,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,9 6,97,98,99,100);

The numbers 1 -100 go in the properties section of the column headings

Male:

TRANSFORM First([Children Query].Age) AS FirstOfAge
SELECT [Children Query].ClientID
FROM [Children Query]
GROUP BY [Children Query].ClientID
PIVOT [Children Query].Expr2 In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2 0,21,22,23,24,25,26,27,28,
29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45 ,46,47,48,49,50,51,52,53,5
4,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70, 71,72,73,74,75,76,77,78,79
,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,9 6,97,98,99,100);

2 Select queries:

Male:

SELECT [Children Query_Crosstab Male].ClientID, [1] & " " & [2] & " " & [3]
& " " & [4] & " " & [5] & " " & [6] & " " & [7] & " " & [8] & " " & [9] & "
" & [10] & " " & [11] & " " & [12] & " " & [13] & " " & [14] & " " & [15] &
" " & [16] & " " & [17] & " " & [18] & " " & [19] & " " & [20] & " " & [21]
& " " & [22] & " " & [23] & " " & [24] & " " & [25] & " " & [26] & " " &
[27] & " " & [28] & " " & [29] & " " & [30] AS [Male Issue], [31] & " " &
[32] & " " & [33] & " " & [34] & " " & [35] & " " & [36] & " " & [37] & " "
& [38] & " " & [39] & " " & [40] & " " & [41] & " " & [42] & " " & [43] & "
" & [44] & " " & [45] & " " & [46] & " " & [47] & " " & [48] & " " & [49] &
" " & [50] & " " & [51] & " " & [52] & " " & [53] & " " & [54] & " " & [55]
& " " & [56] & " " & [57] & " " & [58] & " " & [59] & " " & [60] AS
MaleIssue2, [61] & " " & [62] & " " & [63] & " " & [64] & " " & [65] & " " &
[66] & " " & [67] & " " & [68] & " " & [69] & " " & [70] & " " & [71] & " "
& [72] & " " & [73] & " " & [74] & " " & [75] & " " & [76] & " " & [77] & "
" & [78] & " " & [79] & " " & [80] & " " & [81] & " " & [82] & " " & [83] &
" " & [84] & " " & [85] & " " & [86] & " " & [87] & " " & [88] & " " & [89]
& " " & [90] AS MaleIssue3
FROM [Children Query_Crosstab Male];

Female:

SELECT [Children Query_Crosstab Female].ClientID, [1] & " " & [2] & " " &
[3] & " " & [4] & " " & [5] & " " & [6] & " " & [7] & " " & [8] & " " & [9]
& " " & [10] & " " & [11] & " " & [12] & " " & [13] & " " & [14] & " " &
[15] & " " & [16] & " " & [17] & " " & [18] & " " & [19] & " " & [20] & " "
& [21] & " " & [22] & " " & [23] & " " & [24] & " " & [25] & " " & [26] & "
" & [27] & " " & [28] & " " & [29] & " " & [30] AS [Female Issue], [31] & "
" & [32] & " " & [33] & " " & [34] & " " & [35] & " " & [36] & " " & [37] &
" " & [38] & " " & [39] & " " & [40] & " " & [41] & " " & [42] & " " & [43]
& " " & [44] & " " & [45] & " " & [46] & " " & [47] & " " & [48] & " " &
[49] & " " & [50] & " " & [51] & " " & [52] & " " & [53] & " " & [54] & " "
& [55] & " " & [56] & " " & [57] & " " & [58] & " " & [59] & " " & [60] AS
FemaleIssue2, [61] & " " & [62] & " " & [63] & " " & [64] & " " & [65] & " "
& [66] & " " & [67] & " " & [68] & " " & [69] & " " & [70] & " " & [71] & "
" & [72] & " " & [73] & " " & [74] & " " & [75] & " " & [76] & " " & [77] &
" " & [78] & " " & [79] & " " & [80] & " " & [81] & " " & [82] & " " & [83]
& " " & [84] & " " & [85] & " " & [86] & " " & [87] & " " & [88] & " " &
[89] & " " & [90] AS FemaleIssue3
FROM [Children Query_Crosstab Female];

Then used DLookup in the control source of two text boxes:
Female:

=DLookUp("[Female Issue]+[FemaleIssue2]+[FemaleIssue3]","Female
Issue","[ClientID] =" & Forms![Notification of Death for
Registration]!ClientID)

Male:

=DLookUp("[Male Issue]+[MaleIssue2]+[MaleIssue3]","Male Issue","[ClientID]
=" & Forms![Notification of Death for Registration]!ClientID)

I hope this saves someone the hair pulling I went through!

Cheers,
--
Sharknwfk