Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: Age Range Report / Query

    I have a query with IDnumber and Age. I have 30 records with IDnumber and Ages. How can I do an age-range report or query? E.g. number of people aged 20-25, 26-30, 31-35, 26-40 etc?

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    You Could Either use SQL Like this

    SELECT DISTINCT IIf([AGE] Mod 4<>0,Left(([AGE]/4)+1,1),Left(([AGE]/4),1)) AS [Band], Switch([Band]="1","0 - 4 Year",[Band]="2","5-8 Years") AS AgeBand
    FROM YourTable;

    Expanding the Switch Statements for all your Bands or you could Write a Select Case Function in VBA And use this

    HTH

    Dave

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Copy the following code and put it into a new module:

    Code:
    Public Function AgeRange(lngAge As Long) As String
    
        Select Case lngAge
        
            Case Is < 13
                AgeRange = "0 - 12"
            Case Is < 18
                AgeRange = "13 - 17"
            Case Is < 26
                AgeRange = "18 - 25"
            Case Is < 31
                AgeRange = "26 - 30"
            Case Is < 41
                AgeRange = "31 - 40"
            Case Is < 51
                AgeRange = "41 - 50"
            Case Is < 61
                AgeRange = "51 - 60"
            Case Is < 71
                AgeRange = "61 - 70"
            Case Is < 81
                AgeRange = "71 - 80"
            Case Is < 91
                AgeRange = "81 - 90"
            Case Else
                AgeRange = "91+"
        End Select
        
    End Function
    Modify the ranges that you want. Then in your query add a field like:

    Range:AgeRange([Age])

    That will call the AgeRange function for each Age field and return the range. When I do Age Range reports I create a lookup table with each range and the sort order for each, because the age ranges never sort properly without a forced sort order.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •