Age Group Calculation

[Guest]

I would like count the number of clients in my table that fall into the
following age groups in my query or report. 62 and over, 51-61, 31-50,
18-30, 13-17, 6-12, 1-5, Under 1. I currently run 8 queries with the count
feature using Age Criteria changing each time and based off of the Birthdates
statement Age(DateDiff(“dâ€,[Birthdate],Now())\365.25))). Any ideas on how
this can be done in one report if I display all clients and write
calculations on the bottom our report or somehow group them?

 

[John Spencer]

DateDiff("d",[Birthdate],Now())\365.25

Replace X with your age formula and you should be able to use a formula like
the following.
CountUnder
Count(IIF(X <1, 1,Null))

Count1_5
Count(IIF(X Between 1 and 5, 1,Null))

You can use that formula as a control source in a report.

Optionally if you want to do this in a query, you can use a table with
equivalent age ranges. Your table can have a couple of different
structures. The simplest would be
Age and AgeGroup with one entry for every integer age and the equivalent Age
Group name . Then in a query you could use a subquery to get the agegroup

SELECT (SELECT AgeGroup FROM AgeGroupTable WHERE Age = DateDiff("d",[YourTable].[Birthdate],Now())\365.25)
FROM YourTable


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Michel Walsh]

Define a working table for the groups:


AgeGroups ' table name
GroupID maxAge minAge ' fields name
1 999 62
2 61 51
3 50 31
4 18 30
5 17 13
6 12 6
7 5 1
8 1 0




Assuming you already have the ages of the clients, then try something like:


SELECT groupID, COUNT(*)
FROM originalTable INNER JOIN ageGroups
ON originalTable.Age >= ageGroups.minAge
AND originalTable.Age <= ageGroups.maxAge
GROUP BY groupID



Note that you can make super-groups, by adding the right records in
AgeGroups, like:


9 17 0 ' not having the right to vote


Note that it is your job to add the required logic if you don't want such
age-overlap, over many groups, on the other hand.




You can also use a label, in addition to the numerical ID:


GroupID maxAge minAge label ' fields name
1 999 62 62+
....



and use:


SELECT LAST(label), COUNT(*)
FROM originalTable INNER JOIN ageGroups
ON originalTable.Age >= ageGroups.minAge
AND originalTable.Age <= ageGroups.maxAge
GROUP BY groupID



instead of the preceeding query.




Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Rose,

The method you are using to calculate age is not very accurate. I'd
recommend a function similiar to the following.

Public Function fnAge(DOB As Variant, Optional When As Variant = Null)

'If the DOB field is blank, return a negative value
If isNull(DOB) then
fnAge = -1
exit function
endif

'If no comparison date is passed, use the current date
If IsNull(When) Then When = Date

fnAge = DateDiff("yyyy", DOB, When) _
+ (DateSerial(Year(Date), Month(DOB), Day(DOB)) > _
DateSerial(Year(Date), Month(When), Day(When)))
If fnAge < 0 Then fnAge = -1

End Function

You can pass this function two dates to compute the age of a person based on
their DOB and on some user specified date. Or, if you just pass the DOB, it
will calculate the age as of todays date.

This function computes the number of years between the first and second
date. unfortunately, it does this comparison based on the year of the two
dates, not the entire date (so Datediff("yyyy", #12/30/06#, #01/01/07#) will
actually indicate 1 year). So you have to determine whether the Month/day
combination of the DOB is greater than the Month/Day combination of the Test
date).

BTW, this will return a (-1) if the second date is less than the first.

HTH
Dale