Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

age groups 1

Status
Not open for further replies.

din2005

Programmer
Mar 22, 2005
162
GB
Hi all i have query which looks for patients under 18 and i would like them to be grouped between certain ages?

for eg.

ages howmany
0-4
5-10
11-18

the sql code i have got is this so far..

SELECT PATIENTS.Hospital_no, PATIENTS.firstname, PATIENTS.Surname, TREATMENT.TREATDATE, TREATMENT.AGE, TREATMENT.[NO TREATMENT GIVEN]
FROM PATIENTS INNER JOIN TREATMENT ON PATIENTS.Hospital_no = TREATMENT.HOSP_NO
GROUP BY PATIENTS.Hospital_no, PATIENTS.firstname, PATIENTS.Surname, TREATMENT.TREATDATE, TREATMENT.AGE, TREATMENT.[NO TREATMENT GIVEN]
HAVING (((TREATMENT.TREATDATE) Between #4/1/2004# And #4/1/2005#) AND ((TREATMENT.AGE)<18) AND ((TREATMENT.[NO TREATMENT GIVEN])=False))
ORDER BY TREATMENT.TREATDATE;
 
I would create a function to establish your grouping. Something like this, in a standard code module should work (this is untested, but should at least get you started):

Code:
Function AgeGrouping(Age as Integer)

dim intOut as Integer

If Age >= 0 and Age < 5 Then
intOut = 0
ElseIf Age >= 5 and Age < 11 Then
intOut = 1
ElseIf Age >=12 and Age < 18 Then
intOut = 2
End If

AgeGrouping = intOut

end function

You would then call it like this in your query:

Code:
Group by AgeGrouping(AgeColumn)

The alternative is to nest a bunch of iif's together, but I like to stay away from that.

Hope this helps,

ALex

Ignorance of certain subjects is a great part of wisdom
 
or you can have 3 extra calculated fields, each having a 1 or 0 to specify if they are in age group 0-4, 5-10, 11-18...

--------------------
Procrastinate Now!
 
Is it possible to use iif statement and could you show me example?

cheers

 
I'd have to agree with alex that nested iifs are best avoided, but if you want an example...

iif(
value between date1 and date2,
iif(
value between date2 and date3,
'some result'
),
'some other result'
)

--------------------
Procrastinate Now!
 
I like Alex's solution however would substitute Select Case for the If/ElseIf syntax.

Since the age ranges will change in the future, I would probably create a table of age ranges so I could maintain data rather than code or expressions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
I've always found that custom functions are extremely slow, compared to direct sql, so will try to achieve everything with native sql if at all possible...

--------------------
Procrastinate Now!
 
Duane - Good call on the select case. It really should only be evaluating one condition as well, and placing them in reverse order. I guess that's what I get for 'air coding' though ;-)

Crowley - While in most cases I agree, I will never agree when it comes to using nested iif's. These things are just too darn hard to maintain and update, I think its worth adding an extra second or two (at most) to query execution time to enhance maintainability.

duane's suggestion is definitely the best way to go here. I was hesitant to post this link, because it is using SQL server syntax, but since the subject has been brought up, I think you may find this useful din2005:
note taht case... when is the SQL Server equivalent to Access' iif function.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom
 
I'm talking about adding 3 new calculated columns instead of using nested iifs. nested iifs themselves are *I think* a custom function within access...

--------------------
Procrastinate Now!
 
for eg.
ages howmany
0-4
5-10
11-18

SELECT IIf(Age<5,'0-4',IIf(Age<11,'5-10','11-18')) AS ages
, Count(*) AS howmany
FROM TREATMENT
WHERE TREATDATE Between #2004-04-01# And #2005-04-01#
AND AGE < 18 AND [NO TREATMENT GIVEN])=False
GROUP BY IIf(Age<5,'0-4',IIf(Age<11,'5-10','11-18'));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
If you could partition the age by even increments then there is a Partition Function. For example, start at 0 and go to age 85 in 5 years intrevals.

SELECT DISTINCTROW Partition([age],0,85,5) AS Range, Count([age]) AS [Count]
FROM Categorys
GROUP BY Partition([age],0,85,5);
 
OOps, sorry for the typo:
SELECT IIf(Age<5,'0-4',IIf(Age<11,'5-10','11-18')) AS ages
, Count(*) AS howmany
FROM TREATMENT
WHERE TREATDATE Between #2004-04-01# And #2005-04-01#
AND AGE < 18 AND [NO TREATMENT GIVEN]=False
GROUP BY IIf(Age<5,'0-4',IIf(Age<11,'5-10','11-18'));
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top