Grouping Data

[StephenD]

Does anybody know what is the best way to group data in a query??

I have a big chuck of data with a date of birth field-I want to add an age group field based on the date of birth e.g 0-17 years, 18-24 years and so on.
Can you do it using IIf or is there another way???

Thanks!

 

[Pat Hartman]

Yes, use an IIf().

IIf(CalcAge(BirthDate) <= 17,"A", IIf(CalcAge(BirthDate) > 17 And CalcAge(BirthDate) <= 24, "B", "C"))

Write the CalcAge function and store it as Public in a standard code module. If the IIf() is too complex, you can calculate the whole thing within a function.

Public Function AgeGroup(BirthDate as Date), as String
Select Case CalcAge(BirthDate)
    Case 0 to 17
        AgeGroup = "A"
    Case 18 to 24
        AgeGroup = "B"
    Case 25 to 30
        AgeGroup = "C"
    Case Else
        AgeGroup = "D"
End Select
End Function

 

[StephenD]

That's great, thanks Pat I will go and try this out!