pe*****@cyllene.uwa.edu.au wrote:
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?
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.