By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,120 Members | 1,799 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,120 IT Pros & Developers. It's quick & easy.

Concatenate rows from same column

P: n/a
Hi,

Hoping someone can help.

I am trying to concatenate the ages of still living children divided into
male & female.

So, male 23, female 25, male 13, female 18 should look like this.

Female: 25, 18

Male: 23, 13

This is the query I have that has achieved this but in columns, not rows.

SELECT [Client Details].ClientID, Children.ChildID, Children.FirstName,
Children.[Middle Name], Children.LastName, Children.Age, Children.ChildSex,
Children.DateOfBirth, Children.[Still Living], IIf([Still Living]=True And
[ChildSex]="f",[Age],Null) AS Expr1, IIf([Still Living]=True And
[ChildSex]="m",[Age],Null) AS Expr2
FROM [Client Details] RIGHT JOIN Children ON [Client Details].ClientID =
Children.ChildID;
Any or all advice gratefully recieved.
--
Sharknwfk
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Thanks to both. Will try these options.
--
Sharknwfk
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:N5********************@rogers.com...
See whether the code in http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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

Hoping someone can help.

I am trying to concatenate the ages of still living children divided into male & female.

So, male 23, female 25, male 13, female 18 should look like this.

Female: 25, 18

Male: 23, 13

This is the query I have that has achieved this but in columns, not rows.
SELECT [Client Details].ClientID, Children.ChildID, Children.FirstName,
Children.[Middle Name], Children.LastName, Children.Age,

Children.ChildSex,
Children.DateOfBirth, Children.[Still Living], IIf([Still Living]=True And [ChildSex]="f",[Age],Null) AS Expr1, IIf([Still Living]=True And
[ChildSex]="m",[Age],Null) AS Expr2
FROM [Client Details] RIGHT JOIN Children ON [Client Details].ClientID =
Children.ChildID;
Any or all advice gratefully recieved.
--
Sharknwfk


Nov 13 '05 #2

P: n/a
See whether the code in http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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

Hoping someone can help.

I am trying to concatenate the ages of still living children divided into
male & female.

So, male 23, female 25, male 13, female 18 should look like this.

Female: 25, 18

Male: 23, 13

This is the query I have that has achieved this but in columns, not rows.

SELECT [Client Details].ClientID, Children.ChildID, Children.FirstName,
Children.[Middle Name], Children.LastName, Children.Age, Children.ChildSex, Children.DateOfBirth, Children.[Still Living], IIf([Still Living]=True And
[ChildSex]="f",[Age],Null) AS Expr1, IIf([Still Living]=True And
[ChildSex]="m",[Age],Null) AS Expr2
FROM [Client Details] RIGHT JOIN Children ON [Client Details].ClientID =
Children.ChildID;
Any or all advice gratefully recieved.
--
Sharknwfk

Nov 13 '05 #3

P: n/a
Thanks to both. Will try these options.
--
Sharknwfk
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:N5********************@rogers.com...
See whether the code in http://www.mvps.org/access/modules/mdl0004.htm or
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web" helps.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

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

Hoping someone can help.

I am trying to concatenate the ages of still living children divided into male & female.

So, male 23, female 25, male 13, female 18 should look like this.

Female: 25, 18

Male: 23, 13

This is the query I have that has achieved this but in columns, not rows.
SELECT [Client Details].ClientID, Children.ChildID, Children.FirstName,
Children.[Middle Name], Children.LastName, Children.Age,

Children.ChildSex,
Children.DateOfBirth, Children.[Still Living], IIf([Still Living]=True And [ChildSex]="f",[Age],Null) AS Expr1, IIf([Still Living]=True And
[ChildSex]="m",[Age],Null) AS Expr2
FROM [Client Details] RIGHT JOIN Children ON [Client Details].ClientID =
Children.ChildID;
Any or all advice gratefully recieved.
--
Sharknwfk


Nov 13 '05 #4

P: n/a

"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.


Due to my limited skills, I plumped for this option. I split it into 2
crosstabs, 1 male & 1 female and then recombined the crosstabs as a select
query. Voila.

SQL below:

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);

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);

Combine two into select query:

SELECT [Female Issue].ClientID, [Female Issue].[Female Issue], [Male
Issue].[Male Issue]
FROM [Female Issue] INNER JOIN [Male Issue] ON [Female Issue].ClientID =
[Male Issue].ClientID;

The numbers 1 - 100 are put into Expr1 & 2's properties in the crosstabs as
column headings.

You never know, surviving kids could reach 100 in the future!

The reason I posted the code is I hate it when someone posts a possible
solution and then you never hear whether it was successful or how they
finally achieved it.
Cheers,
--
Sharknwfk
Nov 13 '05 #5

P: n/a

"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.


Due to my limited skills, I plumped for this option. I split it into 2
crosstabs, 1 male & 1 female and then recombined the crosstabs as a select
query. Voila.

SQL below:

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);

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);

Combine two into select query:

SELECT [Female Issue].ClientID, [Female Issue].[Female Issue], [Male
Issue].[Male Issue]
FROM [Female Issue] INNER JOIN [Male Issue] ON [Female Issue].ClientID =
[Male Issue].ClientID;

The numbers 1 - 100 are put into Expr1 & 2's properties in the crosstabs as
column headings.

You never know, surviving kids could reach 100 in the future!

The reason I posted the code is I hate it when someone posts a possible
solution and then you never hear whether it was successful or how they
finally achieved it.
Cheers,
--
Sharknwfk
Nov 13 '05 #6

P: n/a

"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.
Due to my limited skills, I plumped for this option. I split it into 2
crosstabs, 1 male & 1 female and then recombined the crosstabs as a

selectquery. Voila.


Congratulations! And thanks for letting us know it worked, and how
you did it.


Oops, spoke too soon. Too many fields defined. I'll get there and then post
back.

--
Sharknwfk


Nov 13 '05 #7

P: n/a

"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.
Due to my limited skills, I plumped for this option. I split it into 2
crosstabs, 1 male & 1 female and then recombined the crosstabs as a

selectquery. Voila.


Congratulations! And thanks for letting us know it worked, and how
you did it.


Oops, spoke too soon. Too many fields defined. I'll get there and then post
back.

--
Sharknwfk


Nov 13 '05 #8

P: n/a

"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.
Due to my limited skills, I plumped for this option. I split it into 2
crosstabs, 1 male & 1 female and then recombined the crosstabs as a selectquery. Voila.


Congratulations! And thanks for letting us know it worked, and how
you did it.


Oops, spoke too soon. Too many fields defined. I'll get there and then

post back.


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


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.