473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Concatenate rows from same column

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
8 11102
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
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
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

"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

"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

"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

"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

"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Nick Heppleston | last post by:
I have a concatenation problem and I was wondering if somebody might be able to offer some help :-) I have the following table structure holding product long descriptions: Part...
8
by: pb648174 | last post by:
I have a single update statement that updates the same column multiple times in the same update statement. Basically i have a column that looks like .1.2.3.4. which are id references that need to...
6
by: christopher.secord | last post by:
Is there any advantage to doing this: ALTER TABLE testtable ADD CONSTRAINT PK_sysUser PRIMARY KEY NONCLUSTERED (UserID) WITH FILLFACTOR = 100, CONSTRAINT IX_sysUser UNIQUE NONCLUSTERED...
3
by: Krish | last post by:
I have created 2 tables, both tables have the same column names (property ColumnName) it is priceDate,priceTime and priceValue I get an error saying that Column priceDate already belongs to...
5
by: Marina | last post by:
Hi, Let's say I bind the same column to multiple properties of one control (or even to the same property of several controls). If a user changes the value of the property in one control - I...
0
by: Asfar | last post by:
I have a databound column in DataGridView. Is there a way to have different cell types in the same column? Thanks, -Asfar
2
by: Volition | last post by:
I have looked around and can't find any help for my SQL problem. I have a Table which lists people peopledb. _____________ id | Name 1 | Fred 2 | Bill 3 | Bob
5
by: stevepl7 | last post by:
I want to make the rows become column headings in an Access table or query. The data changes based on input to a form. It looks like this: A B N1 1 N2 2 N3 3 N4 4 Where A and...
1
by: pl1 | last post by:
Hi everyone, I'm using MS-Access 2003, OS: Window XP Professional sp3. In Northwind database (sample Database),the Suppliers table have column "SupplierID" which is also present in Products...
2
by: perhapscwk | last post by:
vba, how to use vba to search how many distinct amount in same column? for example.. column C have amount 123 123 444 123 445
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.