Need help grouping by age range 1

[SmallTime]

I’m trying to produce a query (probably crosstab) which displays the number of applicants groped by age range and ethnicity.

The two relevant fields in table ‘TblApplicant’ are ‘AgeAtApplic’ which is a number data type and ‘Ethnicity’ for which the data type is text.

I’d like to ages to be grouped some like this;

16-17
18-25
26-44
45-59

I’m hoping to get the result looking something like this

|Irish | Spanish
16-17 |4 |6
18-25 |3 |8
26-44 |8 |9
45-59 |12 |10 and so on…


Producing the ethnicity and count has been fairly strait forward but I can’t get my head around how to group the ages (first column).

Any ideas would be greatly appreciated.

Kindest regards to all.

 

[lupins46]

Assuming you have an Age field then you would create a formula field, and group on that (or use it in a crosstab)

One example:

if {Mytable.Myagefield) >=16 and {Mytable.Myagefield)<18 then
"16-17"
else if {Mytable.Myagefield) >=18 and {Mytable.Myagefield)<26 then
"18-25"
else if {Mytable.Myagefield) >=26 and {Mytable.Myagefield)<44 then
"26-44"
else if {Mytable.Myagefield) >=44 and {Mytable.Myagefield)<59 then
"44-59"
else
"Over 59"

 

[lupins46]

Sorry - ignore that answer - I'm answering in the wrong forum!

 

[lupins46]

(This is for the Access forum)

You should base your crosstab on a query which has already allocated the item to the age group.

In a standard module create a function to calcylkate the group:

Function AgeGp(vAge)
if vAge<16 then
AgeGp ="00-16"
Elseif vAge >=16 and vAge)<18 then
AgeGp ="16-17"
elseif vAge >=18 and vAge <26 then
AgeGp ="18-25"
elseif vAge >=26 and vAge<44 then
AgeGp ="26-44"
elseif vAge >=44 and vAge<59 then
AgeGp ="44-59"
else
AgeGp ="60 & Over"
end if
end function

In the query, you add an extra column:

MyAge:AgeGp(AgeAtApplic)

You will then have the grouping ready for your crosstab.

 

[jebry]

Hi!

You can still do this with nested IIfs. This is how it would look in the query design view:

MyNewAgeField: IIf(Age>=17 and Age<18, "16-17", IIf(Age>=18 and Age<26, "18-25",etc))))

In SQL

IIf(Age>=17 and Age<18, "16-17", IIf(Age>=18 and Age<26, "18-25",etc)))) As MyNewAgeField

Alternatively, you can program this in a public function which you would call from the query. If you prefer this method, let me know.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[SmallTime]

Many Thanks lupins46, that works just as I needed.
I could have sat here till the cows came home and wouldn't have worked it out on my own.

Also thanks Jeff for your helpful advice an offer.

(apologies if I've posted in the wrong forum)

Note if anyone else uses this, there are a couple of minor syntax errors in Line 4. But take it from me; it's a nice solution.