Extract age profiles of people

[Richie27]

Hi All,

I have a database of people with various details about them, including their date of birth, in one table.

I need to extract a report from this database which shows an age profile of all the people that are in it - maybe like a bar chart or perhaps a list of all the age ranges like:

Age 10-20: 15 people


Age 20-30: 19 people
Age 30-40: 30 people

Any ideas on how to approach this would be great.

My little brain is thinking arrays and loops will be needed............not yet sure how they work in vba yet though!

[jzwp11]

No need for VBA. Start by creating a table that has the age groups

tblAgeGroups
-pkAgeGrpID primary key, autonumber
-txtAgeGroupName (like your Age 10-20)
-longLowerLimit
-longUpperLimit

The records in the above table would look like this:

pkAgeGrpID|txtAgeGroupName|longLowerLimit|longUppe rLimit
1|Age 10-20|10|20
2|Age 20-30|20|30
etc.

Create a query that determines the age of each person. In that query you will need to nest another query that lookups up the appropriate age group

query name: qryPeopleAgeGroup
SELECT datediff("m",dteDOB, date())/12 as CurrentAge, (Select pkAgeGrpID FROM tblAgeGroups WHERE datediff("m",dteDOB, date())/12 >=longLowerLimit AND datediff("m",dteDOB, date())/12<longUpperLimit) as AgeGroup
FROM tblPeople;


Next create a query based on the above that groups and counts by AgeGroup

query name: qryCountByAgeGroup
SELECT qryPeopleAgeGroup.AgeGroup, Count(qryPeopleAgeGroup.AgeGroup) AS CountOfAgeGroup
FROM qryPeopleAgeGroup
GROUP BY qryPeopleAgeGroup.AgeGroup;


Now assuming that you want to show a count for every age group even if the count is zero then you would need 1 final query. This requires a LEFT JOIN and a couple of built-in functions that are nested together

query name: qryFinal

SELECT tblAgeGroups.txtAgeGroupName, IIF(isnull(qryCountByAgeGroup.CountOfAgeGroup),0,q ryCountByAgeGroup.CountOfAgeGroup) as AgeGroupCount
FROM tblAgeGroups LEFT JOIN qryCountByAgeGroup ON tblAgeGroups.pkAgeGrpID = qryCountByAgeGroup.AgeGroup;


I've attached an example database as well.

[Richie27]

Wow, another great one! Never thought of making a table with those values!!!

Hmmmm, I must learn to think outside the box!!

Thanks again for the invaluable lesson!

Rich

[jzwp11]

You're welcome.