Hi,
Essentially you will need to set up a summary query and a subreport
based on it, probably placing the subreport in the report footer. Note that
subreport itself could be a stand-alone report.
However, there is an issue here that you should take into
consideration. An age field will rapidly become out-of-date. Suppose you
have someone who's birthday was April 30, 1989. If you run the report today
(April 29, 2009) he is 19 years old. If you run it tomorrow, he is 20. Will
your age field have been updated. It is better to use a date-of-birth field
and calculate the age on the fly. So, if you have a date-of-birth field,
your summary query might look something like this:
SELECT
Year(Date())-Year([DATE_OF_BIRTH])-IIf(Month([DATE_OF_BIRTH])>Month(Date()),1,IIf(Month([DATE_OF_BIRTH])=Month(Date())
And Day([DATE_OF_BIRTH])>Day(Date()),1,0)) AS CURRENT_AGE, Count(*) AS
PERSON_COUNT
FROM tblPEOPLE
GROUP BY
Year(Date())-Year([DATE_OF_BIRTH])-IIf(Month([DATE_OF_BIRTH])>Month(Date()),1,IIf(Month([DATE_OF_BIRTH])=Month(Date())
And Day([DATE_OF_BIRTH])>Day(Date()),1,0));
Note that I use a complicated formula for calculating the person's age
instead of the DateDiff() function as the DateDiff() function returns an
inaccurate age when the birthday has not yet happened in the current year.
For example, DateDiff("yyyy", #4/29/2009#, #4/30/1989#) returns 20 when the
actual age is 19.
One further note. If you are in a country where your age is considered
to be 1 in your first year of life, instead of 0 as in the USA, you will need
to adjust the formula by adding 1 to it.
Hope this helps,
Clifford Bass
