Producing an age profile for members of a club

I want to produce age profile information from a group of club members
with recorded dates of birth.
I can create a selct query to count the number of members between 20
and 30 years of age (for example) OK.
I want to count them for 20-30 and 30-40 and 40-50 etc and can only do
this by repeating the query (or SQL) repeatedly.
I am a novice programmer (although a senior citizen) and suspect that
there is an easy way.
If there isn't I would propose to identify the oldest and youngest
members to set the limits, then select an appropriate interval and
repeat the query using the identified parameters. Perhaps I should
create a 'make table' query to collect them and then produce a report
based on the table. This seems like a complicated procedure.
Can anyone help?

re: Producing an age profile for members of a club

You could do this in two queries assuming you had a table called
[members] with a field called 'name' and a field called 'age':

The first query: QueryBandAge
would band the members by age group:

SELECT members.Name,
IIf([age]<10,"0-10",IIf([age]<20,"10-20",IIf([age]<30,"20-30",IIf([age]<40,"30-40",IIf([age]<50,"40-50",IIf([age]<60,"50-60",IIf([age]<70,"60-70",IIf([age]<80,"70-80","80+"))))))))
AS AgeBand
FROM members;

The second query: QueryCountBand
would count the members in each band:

SELECT QueryBandAge.AgeBand, Count(QueryBandAge.AgeBand) AS
CountOfAgeBand
FROM QueryBandAge
GROUP BY QueryBandAge.AgeBand;

I hope that is fairly clear but please ask if you require any further
help. Assuming you have that initial table Member with the fields name
and age in your database you could just cut and paste the sql above
into an empty query. If your table is named something else then you
will need to change the names of the tables and fields in the sql.

DG.

re: Producing an age profile for members of a club

<petersk@cyllene.uwa.edu.au> wrote in message
news:1130400846.391795.315720@g47g2000cwa.googlegr oups.com...[color=blue]
> I want to produce age profile information from a group of club members
> with recorded dates of birth.
> I can create a selct query to count the number of members between 20
> and 30 years of age (for example) OK.
> I want to count them for 20-30 and 30-40 and 40-50 etc and can only do
> this by repeating the query (or SQL) repeatedly.
> I am a novice programmer (although a senior citizen) and suspect that
> there is an easy way.
> If there isn't I would propose to identify the oldest and youngest
> members to set the limits, then select an appropriate interval and
> repeat the query using the identified parameters. Perhaps I should
> create a 'make table' query to collect them and then produce a report
> based on the table. This seems like a complicated procedure.
> Can anyone help?
>[/color]
If you're comfortable creating a VBA function in a module, then I would use
the following
(caution, Air Code)

Public Function AgeBand (intAge as integer) as string
Select Case intAge
Case <=20
AgeBand = "Under 20"
Case <=30
AgeBand = "21-30"
Case <=40
AgeBand = "31-40"
Case <=50
AgeBand = "41-50"
Case Else
AgeBand = "Over 50"
End Select
End Function

Then, create a TOTAL query using a GroupBy of AgeBand([Age]).

Good Luck,
Fred Zuckerman

re: Producing an age profile for members of a club

petersk@cyllene.uwa.edu.au wrote:[color=blue]
> I want to produce age profile information from a group of club members
> with recorded dates of birth.
> I can create a selct query to count the number of members between 20
> and 30 years of age (for example) OK.
> I want to count them for 20-30 and 30-40 and 40-50 etc and can only do
> this by repeating the query (or SQL) repeatedly.
> I am a novice programmer (although a senior citizen) and suspect that
> there is an easy way.
> If there isn't I would propose to identify the oldest and youngest
> members to set the limits, then select an appropriate interval and
> repeat the query using the identified parameters. Perhaps I should
> create a 'make table' query to collect them and then produce a report
> based on the table. This seems like a complicated procedure.
> Can anyone help?
>[/color]

The answers you already got here should both work.

I just want to offer one more alternative. If your age groups will be
all the same size (except possibly the oldest and youngest groups),
Access's Partition function is probably the simplest way of figuring out
each person's age group from their age.

For example the following SQL would count the numbers of people in age
groups 0-19, 20-29, 30-39, 40-49, etc in 10-year groups up to age 79,
then ages 80 upwards in one group:


SELECT Partition([age],20,80,10) AS AgeGroup,
Count([your_key_field_name]) as [NumberInAgeGroup]
FROM [your_table_name]
GROUP BY Partition([age],20,80,10)

Just replace your_key_field_name and your_table_name with the
appropriate names from your own data.

More info about the partition function can be found in the VBA help files.

re: Producing an age profile for members of a club

Thanks to all for prompt responses. All seem good and I will give them
all a try (just to familiarise myself so that when such a problem comes
up again I have some experience. Best regards