Find the number of people in each age range

[Guest]

I have an Access database with the details of 100 people on it, including
their ages.
I want to write an query to calculate how many people are in their teens,
20s, 30s etc, but can't work out how to do it.

 

[Guest]

First, unless your database is only going to be used for a very brief time,
storing ages is not a good thing. The best solution is to store their date
of birth, which can be computed into an age whenever is necessary.

Given your current structure, you could do something like:

SELECT (INT(Age/10) * 10) as AgeGroup, Count(LastName) as Frequency
FROM yourTable
GROUP BY (INT(Age/10) * 10)

Another way to handle this would be to create a AgeRange table that contains
three columns(RangeDesc, LowerLimit, UpperLimit). Then, you could define any
number of ranges of unequal number where the values might look like:

"Juvenile", 0, 12
"Teens", 13, 19
"20's", 20, 29
"30's", 30, 39
"40's", 40, 49
"Old Farts", 50, 150 <- I fall in this group

The query with this table might look like:

SELECT AgeRange.RangeDesc, Count(LastName) as Frequency
FROM AgeRange, YourTable
Where YourTable.Age>= AgeRange.LowerLimit
AND YourTable.Age <= AgeRange.UpperLimit
GROUP BY AgeRange.RangeDesc

HTH
Dale