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
8  11104 
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
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
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
"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
"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
"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
"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
"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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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
|
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...
|
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...
|
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
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
