Calculated Fields in Reports

  • Thread starter Thread starter Lynn
  • Start date Start date
L

Lynn

I have a table that contains client information,
including a "date of birth" field. I have a report
already designed and I want to add a section in the
report footer that breaks down the number of clients by
age group (i.e., birth - 5, 6 - 17, 18 - 44). Apparently
I am dazed & confused because I cannot get the formula
correct. Can anyone help?
 
Lynn said:
I have a table that contains client information,
including a "date of birth" field. I have a report
already designed and I want to add a section in the
report footer that breaks down the number of clients by
age group (i.e., birth - 5, 6 - 17, 18 - 44). Apparently
I am dazed & confused because I cannot get the formula
correct.
Click to expand...


That's OK, converting from a bunch of values to a grouping
can be a confusing thing.

Your best bet is to create a subreport for the summary data.
Base the the new report on a Totals type query.

If you only have a (very) small number of groups, you can
use a quick and dirty approach. Add a calculated field to
the report's RecordSource query:

AgeGroup: IIf(age<=5,"0-5", IIf(age<=17,"6-17","18 & over"))
Group By
GrpCountL Count(*)

A better, more flexible approach is to create a table that
defines the age groupings.

table AgeGroups with the following fields:
Descr Text
Low Integer
High Integer

with records:
0-5 0 5
6-17 6 17
18-44 18 44
. . .

Then join that table to the table you're currently using
with an nonequi Join. The query would be something along
these lines:

SELECT AgeGroups.Descr, Count(*) As GrpCountL
FROM clients INNER JOIN AgeGroups
ON Age(DOB) Between AgeGroups.Low
And AgeGroups.High
GROUP BY AgeGroups.Descr

(You'll have to create your own expression to calculate the
age value)
 
Back
Top