Welcome to the forum!
First, I would not store the age since it is a value that changes constantly. It would be better to store the date of birth. With the date of birth, you can calculate the age of a person at any point in time.
Regarding the age ranges, I would probably set them up in a table.
tblAgeGroups
-pkAgeGroupID primary key autonumber
-txtAgeGroupName (this would be optional)
-longLowerLimit
-longUpperLimit
Technically speaking the above table is not normalized since each age group has 2 age limits (one-to-many relationship), but to keep things simple, we'll leave the table unnormalized.
For reference, the normalized structure would look like this:
tblAgeGroups
-pkAgeGroupID primary key, autonumber
-txtAgeGroupName
tblAgeGroupLimits
-pkAgeGroupLimitID primary key, autonumber
-longAge
-limit (just a field to distinguish whether the age is the lower or upper limit)
Now there are many ways to calculate the age of a person given their date of birth. I found this custom function a while back. You can actually use it to find the age of person at any point in time not just relative to today's date.
Code:
Public Function GetAge(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if missing.
'Return: Whole number of years.
Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.
GetAge = Null 'Initialize to Null
'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB
If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If
If dtAsOf >= dtDOB Then 'Calculate only if it's after person was born.
dtBDay = DateSerial(Year(dtAsOf), Month(dtDOB), Day(dtDOB))
GetAge = DateDiff("yyyy", dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If
End If
End Function
You would create a new module and add this code to it; then you can call the function whenever you need it.
Now you can create a query that shows the person and their age; something like this:
SELECT tblClient.txtClientFName, tblClient.txtClientLName, tblClient.dteBirth, getage(tblCLient.dteBirth) AS CurrentAge
FROM tblClient;
Now to show which age group they belong to, you would need to nest a query within the above query. I show the nested query in red below
query name: qryShowAgeGroupForEachClient
SELECT tblClient.txtClientFName, tblClient.txtClientLName, tblClient.dteBirth, getage(tblCLient.dteBirth) AS CurrentAge, (Select pkAgeGroupID from tblAgeGroups WHERE getAge(tblCLient.dteBirth) between longlowerlimit and longupperlimit) AS AgeGroup
FROM tblClient;
You can then use the above query to count the number of people in each age group using an aggregate query
query name: qryCountByAgeGroup
SELECT qryShowAgeGroupForEachClient.AgeGroup, Count(qryShowAgeGroupForEachClient.AgeGroup) AS CountOfAgeGroup
FROM qryShowAgeGroupForEachClient
GROUP BY qryShowAgeGroupForEachClient.AgeGroup;
You can then join the above query back to the age group table to get the lower/upper limits for each age group and the count in one place. I assume that if no people fall into a particular age group, you still want to show the age group and a zero for the count. To do this, you have to use a left join between the age group table and the query that does the counting. Furthermore, if there is not count for an age group, the query will return NULL, so you have to use an IIF() function to test for NULL and set the value to zero if true. If false, show the count. That final query will look like this:
query name: qryFinal
SELECT tblAgeGroups.longLowerLimit & "-" & tblAgeGroups.longUpperLimit AS AgeRanges, IIF (isnull(qryCountByAgeGroup.CountOfAgeGroup),0,qryC ountByAgeGroup.CountOfAgeGroup) AS CountByGroup
FROM tblAgeGroups LEFT JOIN qryCountByAgeGroup ON tblAgeGroups.pkAgeGroupID = qryCountByAgeGroup.AgeGroup;
You could then base your report on this final query.
I've attached an example database for reference.