Help... Access Novice! Count ages within different ranges

[Chand]

Hi

I have a field (Age) which calculates the age of clients based on date of birth - Age: DateDiff("yyyy",[Birth Date],Now())

I would like a way of counting the number who fall within different age ranges:

Under 17
18 - 25
26 - 35
36 - 45
46 - 55
56 - 65
65+

Is there an easy way to do this within the same query showing totals within each each range?

Any help would be appreciated. I'm very new to Access!

Many thanks in advance.

 

[bat17]

You can split the ages into bands using nested iif() statements like
ageBand: IIf([age]<17,1,IIf([age]<26,2,(IIf([age]<36,3,IIf([age]<46,4,IIf([age]<56,5,IIf([age]<66,6,7)))))))

You can then count the bands in a totals query. wheter you can do this all in the same query depends on what else you are doing with the query.

Also be aware that using DateDiff is not accurate for birth days, it just compare years, not fractions of a year. So a child born 31 Dec would show as being 1 on the next day (1 Jan)

peter

 

[Abulafia]

I have a field (Age) which calculates the age of clients based on date of birth - Age: DateDiff("yyyy",[Birth Date],Now())

Firstly, your Age expression is not accurate.

 AGE: DateDiff("yyyy", DOB, Now()) + Int(Format(Now(), "mmdd") < Format(DOB, "mmdd"))

This is accurate. Yours only looks at years while the one I've posted does this and also looks at days in the year. i.e. A birthday on the 20th of June, for 1979 with your expression would return 25 for either the 19th or 21st of June when the 19th should return 24. My expression fixes this.

I would like a way of counting the number who fall within different age ranges: 

Under 17
18 - 25
26 - 35
36 - 45
46 - 55
56 - 65
65+

Is there an easy way to do this within the same query showing totals within each each range? [/quote]

Make a function in a standalone module.

i.e.

[code]
Public Function GetCount(intAge As Integer) As Long
    Select Case intAge
        Case < 18
            GetCount = 1
        Case < 26
            GetCount = 2          
        Case < 36
            GetCount = 3
        Case < 46
            GetCount = 4
        Case < 56
            GetCount = 5
        Case < 65
            GetCount = 6
        Case Else
            GetCount = 7
    End Select

End Function[/code]

Now do as bat17 suggests.

You can split the ages into bands using nested iif() statements like
ageBand: IIf([age]<17,1,IIf([age]<26,2,(IIf([age]<36,3,IIf([age]<46,4,IIf([age]<56,5,IIf([age]<66,6,7)))))))

Unless you are going to use one for an either/or situation. Multiple nested IIf statements are going to slow down the performance of a query. Much better to use a custom function this time.