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 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
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: 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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |