age groups

[Linda in Iowa]

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?

 

[Allen Browne]

If your age groups are all multiples of 10, you could partition them into
groups with:
((Date() - MbrBirthdate) / 365.25) \ 10

You can then group on that expression (depress the Totals button on the
query design toolbar), and count the primary key.

 

[John W. Vinson]

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?

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.

 

[Linda in Iowa]

I pasted your SQL statement into a new query and got a data type mismatch in
criteria expression.
Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field
with a value whose data type does not match the field's data type.

I am not good at figuring out these details without a lot of trial and
error, so if you know what i need to do I would like to see what this
statement returns.
thanks

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?

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.

 

[Linda in Iowa]

sorry this reply belongs to the next response. I need to try this one yet.

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?

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.

 

[Linda in Iowa]

I pasted your SQL statement into a new query and got a data type mismatch in
criteria expression.
Data type mismatch in criteria expression. (Error 3464)
The criteria expression in a Find method is attempting to compare a field
with a value whose data type does not match the field's data type.

I am not good at figuring out these details without a lot of trial and
error, so if you know what i need to do I would like to see what this
statement returns.
thanks

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?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

To group on just the Age you must remove the Memtype and ExpDate from
the GROUP BY clause. Using a different age formula (there are more than
365 days in a leap year) and the Partition() function your query would
look like this:

SELECT Partition(Year(Date())-Year(MbrBirthdate) +
(DateSerial(Year(Date()),Month(MbrBirthdate),
Day(MbrBirthdate))>Date()), 0,100,10) As AgeRange,

COUNT(*) AS BirthDays

FROM MBRS

WHERE Memtype<>"comp" AND ExpDate>Date()

GROUP BY Partition(Year(Date())-Year(MbrBirthdate) +
(DateSerial(Year(Date()),Month(MbrBirthdate),
Day(MbrBirthdate))>Date()), 0,100,10)

ORDER BY Partition(Year(Date())-Year(MbrBirthdate) +
(DateSerial(Year(Date()),Month(MbrBirthdate),
Day(MbrBirthdate))>Date()), 0,100,10) DESC;

The Partition() function will return values like this:

0:9
10:19
20:29
30:39
... up to ...
100:100
:101 -> means 101 or older.

I'm not sure the ORDERing will be as expected.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSlq3NoechKqOuFEgEQK6ZgCeLe2lstdxCXWcKVX2b7KpWhdsrmkAoLNU
JC8iep5HQd9t/uV3VjoSJ3rL
=e6ne
-----END PGP SIGNATURE-----

 

[Linda in Iowa]

I created the age groups table as you suggested. Right now I have a query
that shows the age of each member. Do I add the Age Groups table to that
query? I have not been able to get the correct results or I get no
information returned. Do the tables need to be related somehow?
This is the SQL that gives me the member age and number of members for that
age but I have not added anything from the Age Groups table .

SELECT DISTINCTROW
DateDiff("yyyy",[MbrBirthdate],Date())-IIf(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0)
AS memberage,
Count(DateDiff("yyyy",[MbrBirthdate],Date())-IIf(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0))
AS [number of members]
FROM MBRS, [Age Groups]
GROUP BY
DateDiff("yyyy",[MbrBirthdate],Date())-IIf(Format([MbrBirthdate],"mmdd")>Format(Date(),"mmdd"),1,0);

If I add the expression to the age field I get no results. I then added it
to the age group field and came up with incorrect data.
Also what field do I need to use for the count? Also if I change the Top
Values property of the query to 1 I get only one record with incorrect data
whe I can get data.

I am using access 2003.

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?

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.