Results grouped by age groups

[p7eggyc]

Good afternoon! I am having a couple of problems with my SQL on this particular problem. Here is a summary of my query need:

Choose all records from the CouncilMembers table that have a matching intake record. There may be multiple councilmember records for the same intake record. I want to find the earliest council date among the councilmember records and if the earliest date is within the user defined date range, calculate the age of the person as of that council date using the birth date held in the intake table. This is the query I have so far for this and I know how to add the user parameters, I took them out for simplicity.

SELECT d.DocketNum, d.[Council Date] AS minofdate, DateDiff("yyyy",[date of birth],[minofdate]) AS Age, INTAKES.Gender
FROM INTAKES INNER JOIN CouncilMembers AS d ON INTAKES.DocketNum = d.DocketNum
WHERE (((d.[Council Date])=(SELECT min([council date])
FROM [CouncilMembers]
WHERE [docketNum]=d.[DocketNum])));

I want to then create a report that displays the result of this query that displays total #’s separated by gender or unknown gender and grouped by age ranges such as 18-21, etc. I have written the following SQL that brings me back the count of records by age and gender:

SELECT qryAgeAtCouncil.Age, Count(*) AS TotalByAge, qryAgeAtCouncil.Gender
FROM qryAgeAtCouncil
GROUP BY qryAgeAtCouncil.Age, qryAgeAtCouncil.Gender;


My Questions:
1. I need it to group them by age ranges and I can't figure out how to get it to do that.

2. I'd really rather generate all of this in one query. Is that possible??

Thanks for your help!

 

[tlbroadbent]

I recommend creating a function that determines the Age Group. Use the function in the query as follows.

SELECT
AgeGroup(qry.Age) As AgeGrp,
Count(*) AS TotalByAge,
qry.Gender
FROM qryAgeAtCouncil As qry
GROUP BY
AgeGroup(qry.Age),
qry.Gender;

------------- Function -------------

Function AgeGroup(Age as Integer) As String
'Modify as needed to define your age groupings

If Age < 18 Then
AgeGroup = &quot;< 18&quot;
ElseIf Age >= 18 And Age <= 21 Then
AgeGroup = &quot;18-21&quot;
ElseIf Age >= 22 And Age <= 25 Then
AgeGroup = &quot;22-26&quot;
ElseIf Age >= 26 And Age <= 30 Then
AgeGroup = &quot;26-30&quot;
ElseIf Age >= 31 And Age <= 39 Then
AgeGroup = &quot;31-39&quot;
ElseIf Age >= 40 And Age <= 49 Then
AgeGroup = &quot;40-49&quot;
ElseIf Age >= 50 And Age <= 59 Then
AgeGroup = &quot;50-59&quot;
ElseIf Age > 59 Then
AgeGroup = &quot;>59&quot;
End If

End Function
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[p7eggyc]

Ahhhh, that sounds terrific! My only problem now is I can't figure out where to put the function. I tried putting it in a module and I also tried a couple of places in the design grid but alas, no luck. Can I have another tip?? Thanks!

Peggy

 

[p7eggyc]

I'm sorry! I figured it out! Thanks so much for all your help!

Peggy