Group and subtotal names on a report according to age grouping

[MartyBoy]

Hi all learned people
I am trying to create a report that lists members of an organisation grouped by their current age, i.e. under 20, 25 - 29, 30 - 34 etc and provides a subtotal for each age group.
I have read inumerable posts and FAQ's in both the Queries and Reports forums but can't find any matches with my problem.
I have wrestled and repeatedly failed with nested IIf statements in the underlying query. I have also tried using different columns in the query each with an IIf statement selecting a specific age group, but then can't find a way to select the correct column for the report.
A sample IIf statement showing my separate expresion for an age group is as below:

AgeGroup2: IIf(Int((Now()-[DOB])/365.25)>19 And Int((Now()-[DOB])/365.25)<25,"Under 25","")

Any pointers to a way of achieving this?
All contributions gratefully acknowledged in advance.

 

[MartyBoy]

Sorry for the bother, I got the nested IIf statements to work in the query which means I can sort and subtotal by age group on the report. My apologies for the bother, if anyone is interested in the expression code that is now working I am happy to post it.

 

[dhookom]

MartyBoy,
Your calculation of age isn't 100% accurate. There are some good examples of age calculation at

http://www.mvps.org/access/datetime/date0001.htm.

Also, I would not use nested IIf()s since you end up with complex expressions of business rules (age groupings) that will probably change in the future. A better solution involves creating a table of age ranges with MinAge, MaxAge, and AgeTitle fields.

IMHO, the next best solution would be to create a small function that accepts the age or dob and returns the age group. Save this function in a module named "modBusinessCalcs".

Duane
Hook'D on Access
MS Access MVP

 

[MartyBoy]

Thanks Duane
I will check the URL you have supplied re age calculations, and I agree that creating a table with age groups and titles is a much more maintainable and simpler way to go than nested statements, which I hate having to decipher either from my own code or someone elses.

I sincerely appeciate your prompt assistance and for steering me in the right direction.

 

[ugek010]

so what is the expression code you used?...I have a very similar situation and usually I take care of this with code but this time I have no way around but to put the expression at the report level.

Thanks!

 

[dhookom]

This is the expression:

 Age=DateDiff("yyyy", [Bdate], Now())+ Int( Format(now(), "mmdd") < Format( [Bdate], "mmdd") )

Duane
Hook'D on Access
MS Access MVP

 

[MartyBoy]

Hi ugeke010 and Duane
Sorry for delay in reply, been out of town. I have since changed the procedure so that the age group names are now looked up in a table but here is the original convoluted expression for creating the groupings:

AgeGroup: IIf(Int((Now()-[DOB])/365.25)<20,"Under 20",IIf(Int((Now()-[DOB])/365.25)>19 And Int((Now()-[DOB])/365.25)<25,"20 To 24",IIf(Int((Now()-[DOB])/365.25)>24 And Int((Now()-[DOB])/365.25)<30,"25 To 29",IIf(Int((Now()-[DOB])/365.25)>29 And Int((Now()-[DOB])/365.25)<35,"30 To 34",IIf(Int((Now()-[DOB])/365.25)>34 And Int((Now()-[DOB])/365.25)<40,"35 To 39",IIf(Int((Now()-[DOB])/365.25)>39 And Int((Now()-[DOB])/365.25)<45,"40 To 44",IIf(Int((Now()-[DOB])/365.25)>44 And Int((Now()-[DOB])/365.25)<50,"45 To 49",IIf(Int((Now()-[DOB])/365.25)>49 And Int((Now()-[DOB])/365.25)<55,"50 To 54",IIf(Int((Now()-[DOB])/365.25)>54 And Int((Now()-[DOB])/365.25)<60,"55 To 59",IIf(Int((Now()-[DOB])/365.25)>59 And Int((Now()-[DOB])/365.25)<65,"60 To 64",IIf(Int((Now()-[DOB])/365.25)>64,"65 and Over","No DOB")))))))))))

Sorry, but you asked!

 

[dhookom]

I much prefer the lookup table that provides the age ranges. I would hate to maintain an expression that has so many nested IIf()s. Also, the number of days divided by 365.25 is not an accurate method for determining age.

Duane
Hook'D on Access
MS Access MVP