Dates, Quarters and counts

[Guest]

Hi,

I have a list of Dates of Birth in a table. I want to be able to count the
following:

Count of babies within each 3 month period (April-June, July-Sep etc):
Under 6mths
6mths to 12mths
12mths to 18mths
18mths to 24mths
24mths to 36mths

I just can't see where to begin! The query will only list the dates of
birth. I can group on the quarters in reports but can't see how to pull out
the age groups.

Can anyone help?

Thanks

Andy

 

[John Spencer]

First place to begin is how you define the age of the child in months. You
need some cutoff
Born: Nov12, 2004
Age on Dec 31, 2004 (1 month)
Age on Mar 30, 2005 (4 months)
Age on July 31, 2005 (8 months)

Next if you want to use the last day of each period. Then the child will
get counted multiple times - is that what you want?

One fomula for calculating months of age (DOE = Date of event)
intMonthsOld = DateDiff("M", DOB, DOE) + (Day(DOB) > Day(DOE))

Note that if someone is born on Jan 31 the above will return 0 (not 1) if
DOE is Feb 28.

Next is how you define the period range. Is it the prior year? So you want
the statistics for each quarter of 2005?