making age group query work

[Linda in Iowa]

I recently posted this and received a reply as follows. Can anyone tell me
how to make it work as I have not been able to get correct results if any.
I am using Access 2003.

Here is my original post:
----------------------------
I have an age field that is calculated from a birthdate.
I want to count the number of records in age groups such as 10-19, 20-29,
30-39, 40-49.

the Birthdate field has date entered as mm/dd/yyyy
The Age field is calculated by using ((Now()-[MbrBirthdate])/365)

When I specify an age group in the criteria area of the calculated age field
and Count on the Birthdate field it will show each age and count it as 1. I
want the total number of records in that group.

Here is the SQL of the query I currently have:

SELECT DISTINCTROW ((Now()-[MbrBirthdate])/365) AS [Main member age],
Count(MBRS.MbrBirthdate) AS CountOfMbrBirthdate
FROM MBRS
GROUP BY ((Now()-[MbrBirthdate])/365), MBRS.Memtype, MBRS.ExpDate
HAVING (((((Now()-[MbrBirthdate])/365))>69.9 And
(((Now()-[MbrBirthdate])/365))<80) AND ((MBRS.Memtype)<>"comp") AND
((MBRS.ExpDate)>Date()))
ORDER BY ((Now()-[MbrBirthdate])/365) DESC;


Also is it possible to show the counts for all age groups in one query?

Here is the response I am trying to get to work.
---------------------------------------------------
For the most flexibility, I'd suggest an AgeGroups table with two fields:
Age
(Long Integer) and Agegroup (text), with values like

0 "Child"
10 "10-19"
20 "20-29"

and so on.

Since a year isn't exactly 365 days (leap years!), dividing by 365 won't
give
an exact age as of the previous birthday. Instead use an expression:

DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate],
"mmdd")

Format(Date(), "mmdd"), 1, 0)

Include the AgeGroups table in the query with this expression as a criterion

= DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate],
"mmdd") > Format(Date(), "mmdd"), 1, 0)

Sort ascending by Age and set the Top Values property of the query to 1 to
select the age group.
--

 

[Duane Hookom]

How far did you get? Did you create a table of age ranges? I would have added
a field to specify the min and max ages:

tblAgeGroups
AgeMin AgeMax AgeGroup
0 9 "Child"
10 19 "10-19"
..... etc ...
You would calculate the age as a column in your query and set the criteria
under this age column to:
Between AgeMin and AgeMax

--
Duane Hookom
Microsoft Access MVP

I recently posted this and received a reply as follows. Can anyone tell me
how to make it work as I have not been able to get correct results if any.
I am using Access 2003.

Here is my original post:
----------------------------
I have an age field that is calculated from a birthdate.
I want to count the number of records in age groups such as 10-19, 20-29,
30-39, 40-49.

the Birthdate field has date entered as mm/dd/yyyy
The Age field is calculated by using ((Now()-[MbrBirthdate])/365)

When I specify an age group in the criteria area of the calculated age field
and Count on the Birthdate field it will show each age and count it as 1. I
want the total number of records in that group.

Here is the SQL of the query I currently have:

SELECT DISTINCTROW ((Now()-[MbrBirthdate])/365) AS [Main member age],
Count(MBRS.MbrBirthdate) AS CountOfMbrBirthdate
FROM MBRS
GROUP BY ((Now()-[MbrBirthdate])/365), MBRS.Memtype, MBRS.ExpDate
HAVING (((((Now()-[MbrBirthdate])/365))>69.9 And
(((Now()-[MbrBirthdate])/365))<80) AND ((MBRS.Memtype)<>"comp") AND
((MBRS.ExpDate)>Date()))
ORDER BY ((Now()-[MbrBirthdate])/365) DESC;


Also is it possible to show the counts for all age groups in one query?

Here is the response I am trying to get to work.
---------------------------------------------------
For the most flexibility, I'd suggest an AgeGroups table with two fields:
Age
(Long Integer) and Agegroup (text), with values like

0 "Child"
10 "10-19"
20 "20-29"

and so on.

Since a year isn't exactly 365 days (leap years!), dividing by 365 won't
give
an exact age as of the previous birthday. Instead use an expression:

DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate],
"mmdd")

Format(Date(), "mmdd"), 1, 0)

Include the AgeGroups table in the query with this expression as a criterion

= DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate],
"mmdd") > Format(Date(), "mmdd"), 1, 0)

Sort ascending by Age and set the Top Values property of the query to 1 to
select the age group.

 

[John Spencer]

If you are trying to count by decades 0 to 9, 10 to 19, etc you can accomplish
this with just some math

SELECT DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10 AS [Decade],

Count(MBRS.MbrBirthdate) AS CountByDecade
FROM MBRS

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10

If you want to use your formula for age (which is a bit less accurate then)

SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade],
Count(MBRS.MbrBirthdate) AS CountDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY ((DATE()-[MbrBirthdate])/365.25)\10
ORDER BY ((DATE()-[MbrBirthdate])/365.25)\10 DESC;

If you need date ranges that are different than that you would need a table
similar to Duane's. I would modify it slightly and make ageMax the cutoff
value that is for the first group I would have 10 not 9. I would then test in
the query to make sure the Age Group was LESS than the AgeMax. Modifying your
query to use that table

SELECT tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin
Count(MBRS.MbrBirthdate) AS CountOfMbrBirthdate
FROM MBRS INNER JOIN AgeGroup
ON ((Now()-MBRS.[MbrBirthdate])/365) >= tblAgeGroup.AgeMin
AND ((Now()-MBRS.[MbrBirthdate])/365) < tblAgeGroup.AgeMax
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin
ORDER BY tblAgeGroup.AgeMin DESC;


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Linda in Iowa]

Your DateDiff code give me a Syntax error (missing operator) in query
expression 'DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10'.

The SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade], code works fine
but I would like to see how the DateDiff works.

Thanks.

If you are trying to count by decades 0 to 9, 10 to 19, etc you can
accomplish this with just some math

SELECT DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10 AS [Decade],

Count(MBRS.MbrBirthdate) AS CountByDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10

If you want to use your formula for age (which is a bit less accurate
then)

SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade],
Count(MBRS.MbrBirthdate) AS CountDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY ((DATE()-[MbrBirthdate])/365.25)\10
ORDER BY ((DATE()-[MbrBirthdate])/365.25)\10 DESC;

 

[John Spencer]

Looks like an extra > crept into the expression.

DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0)

So unless I've made some other egregious blunder the query should read:

SELECT DateDiff("yyyy",[MbrBirthdate],Date()) -
IIF(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)\10 AS [Decade],
Count(MBRS.MbrBirthdate) AS CountByDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)\10

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Your DateDiff code give me a Syntax error (missing operator) in query
expression 'DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10'.

The SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade], code works fine
but I would like to see how the DateDiff works.

Thanks.

If you are trying to count by decades 0 to 9, 10 to 19, etc you can
accomplish this with just some math

SELECT DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10 AS [Decade],

Count(MBRS.MbrBirthdate) AS CountByDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],

"mmdd") > Format(Date(), "mmdd"), 1, 0)\10

If you want to use your formula for age (which is a bit less accurate
then)

SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade],
Count(MBRS.MbrBirthdate) AS CountDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY ((DATE()-[MbrBirthdate])/365.25)\10
ORDER BY ((DATE()-[MbrBirthdate])/365.25)\10 DESC;

 

[Linda in Iowa]

Thanks again, but the result I get is the number of members for each year of
age. I was hoping for the number of members for each 10 year group: 0-9,
10-19, 20-29 etc.
Linda

 

[John Spencer]

Did you do the integer division by 10?

This gives you the age in years.
DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0)

This gives you the age in decades completed - 0 being 0 to 9, 1 being 10 to
19, etc.
DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0) \ 10

You are doing integer division when you divide by 10 using the reverse slash.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks again, but the result I get is the number of members for each year of
age. I was hoping for the number of members for each 10 year group: 0-9,
10-19, 20-29 etc.
Linda

Looks like an extra > crept into the expression.

DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0)

So unless I've made some other egregious blunder the query should read:

SELECT DateDiff("yyyy",[MbrBirthdate],Date()) -
IIF(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)\10 AS
[Decade],
Count(MBRS.MbrBirthdate) AS CountByDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)\10

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Linda in Iowa]

I solved it by putting parantheses in front of DateDiff and another one just
before \10.
So it looks like this now.
(DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0)) \ 10

Thanks again.

Did you do the integer division by 10?

This gives you the age in years.
DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0)

This gives you the age in decades completed - 0 being 0 to 9, 1 being 10
to 19, etc.
DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0) \ 10

You are doing integer division when you divide by 10 using the reverse
slash.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks again, but the result I get is the number of members for each year
of age. I was hoping for the number of members for each 10 year group:
0-9, 10-19, 20-29 etc.
Linda

Looks like an extra > crept into the expression.

DateDiff("yyyy",[MbrBirthdate], Date())-
IIF(Format([MbrBirthdate],"mmdd") > Format(Date(),"mmdd"),1,0)

So unless I've made some other egregious blunder the query should read:

SELECT DateDiff("yyyy",[MbrBirthdate],Date()) -
IIF(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)\10 AS
[Decade],
Count(MBRS.MbrBirthdate) AS CountByDecade
FROM MBRS
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date()
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) -
IIF(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)\10

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================