age groups

  • Thread starter Thread starter Linda in Iowa
  • Start date Start date
L

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?
 
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.
 
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?
Click to expand...

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)
Click to expand...

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)
Click to expand...

Sort ascending by Age and set the Top Values property of the query to 1 to
select the age group.
 
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


John W. Vinson said:
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?
Click to expand...

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)
Click to expand...

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)
Click to expand...

Sort ascending by Age and set the Top Values property of the query to 1 to
select the age group.
Click to expand...
 
sorry this reply belongs to the next response. I need to try this one yet.

John W. Vinson said:
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?
Click to expand...

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)
Click to expand...

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)
Click to expand...

Sort ascending by Age and set the Top Values property of the query to 1 to
select the age group.
Click to expand...
 
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


MGFoster said:
Linda said:
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?
Click to expand...

-----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-----
Click to expand...
 
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.



John W. Vinson said:
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?
Click to expand...

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)
Click to expand...

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)
Click to expand...

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