calculating age

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am not a programmer and am fumbling my way through building a database. I
have created a query based on a table which has a 'date of birth' field. I
would like to use the expression builder to group the data into age
categories such as < 25, 25-30,30-40 etc. In the past, I have used;
iif(
![dob] is between [date] and [date],"<25",etc etc. This method is
not effective because as time progresses, the dates have to be change. I
realise I could also manually put the dates in everytime I run the query, but
this also is laboursome. Is there away to get the system to recognise that I
want the current day, current month and then hardcode the year into it so
that it automatically calculates ? Hope someone can help.
Thanks !!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use this expression for the age (all one line):

Year(Date())-Year(birthdatefield) + (DateSerial(Year(Date()),
Month(birthdatefield), Day(birthdatefield))>Date())

Substitute the DOB column name for "birthdatefield."

To partition the age use the Partition() function - you will have to
have the same interval between ages. E.g.:

SELECT Partion(Year(Date())-Year(DOB) + (DateSerial(Year(Date()),
Month(DOB),Day(DOB))>Date()), 25, 100, 5) As Ages,
Count(*)
FROM table_name
WHERE < criteria >
GROUP BY Partion(Year(Date())-Year(DOB) + (DateSerial(Year(Date()),
Month(DOB),Day(DOB))>Date()), 25, 100, 5)

The partition example I gave is ages 25 to 100 in 5 year intervals.
Partition() returns values like this:

: 24 - means age is between negative infinity and 24 yrs old
25 : 29 - means age is between 25 and 29 yrs old
100 : - means age is between 100 and positive infinity

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkz2MoechKqOuFEgEQIngACg1hHb18quYn2kNyidSDRTkXnmVJ4AoMjl
3OhcRZ4j4RREE2opxDNazEfV
=aG0t
-----END PGP SIGNATURE-----
 
Thank you.
I used the expression for age in the expression builder and ran the query
and it calculated the ages for me. Great ! I was unsure from that point what
next step to take (the parition lost me a bit) as mentioned I am as amatuer
as they come. However, problem soloved because I think I can just biuld
another query based on the one I created and program it in the expression
builder to categorise the ages for me. Problem solved. Thank you !

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

Use this expression for the age (all one line):

Year(Date())-Year(birthdatefield) + (DateSerial(Year(Date()),
Month(birthdatefield), Day(birthdatefield))>Date())

Substitute the DOB column name for "birthdatefield."

To partition the age use the Partition() function - you will have to
have the same interval between ages. E.g.:

SELECT Partion(Year(Date())-Year(DOB) + (DateSerial(Year(Date()),
Month(DOB),Day(DOB))>Date()), 25, 100, 5) As Ages,
Count(*)
FROM table_name
WHERE < criteria >
GROUP BY Partion(Year(Date())-Year(DOB) + (DateSerial(Year(Date()),
Month(DOB),Day(DOB))>Date()), 25, 100, 5)

The partition example I gave is ages 25 to 100 in 5 year intervals.
Partition() returns values like this:

: 24 - means age is between negative infinity and 24 yrs old
25 : 29 - means age is between 25 and 29 yrs old
100 : - means age is between 100 and positive infinity

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkz2MoechKqOuFEgEQIngACg1hHb18quYn2kNyidSDRTkXnmVJ4AoMjl
3OhcRZ4j4RREE2opxDNazEfV
=aG0t
-----END PGP SIGNATURE-----

Hi,
I am not a programmer and am fumbling my way through building a database. I
have created a query based on a table which has a 'date of birth' field. I
would like to use the expression builder to group the data into age
categories such as < 25, 25-30,30-40 etc. In the past, I have used;
iif(
![dob] is between [date] and [date],"<25",etc etc. This method is
not effective because as time progresses, the dates have to be change. I
realise I could also manually put the dates in everytime I run the query, but
this also is laboursome. Is there away to get the system to recognise that I
want the current day, current month and then hardcode the year into it so
that it automatically calculates ? Hope someone can help.
Thanks !!
Click to expand...
Click to expand...
 
Back
Top