Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Tampa, FL

    Unanswered: Percentage of records help. Oh please.

    I have a database in access 2000. The purpose of this databse is to keep statistical data on a select group of people. I have a query that slects records entered from a certain location in a certain date range.

    The Database has a table named Stats. A field in this table is called Age.
    From the forementioned query I need to determine how many people are aged between 17 and 24. I also need to know how many records there are total and what percentage of the total records are people are aged 17 - 24.

    I need to da the same thing with gender. wich is likely easier (i guess) Since males are stored as a value of 1 and females a value of 2.

    Any help would be greatly appreciated.

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    It sounds like you are going to need several queries. Start with the query that selects the data. It sounds like you already have that. Make a second query that is based on the first and use Count(*) to give you a total number of records that were selected. With the ages, are they age ranges? If so you are going to need a function to calculate the age then put it in a range.

    To calculate the age use:

    Public Function CalculateAge(strBirthDay As Variant) As Long
        Dim datBirthDay As Date
        Dim lngYear As Long
        Dim lngMonth As Long
        Dim lngDay As Long
        Dim lngCurrentYear As Long
        Dim lngCurrentMonth As Long
        Dim lngCurrentDay As Long
        Dim lngAge As Long
        Dim booSubtractYear As Boolean
        If strBirthDay & "" = "" Or strBirthDay = "0" Then
            CalculateAge = -1
            Exit Function
        End If
        'datBirthDay = CDate(Mid(strBirthDay, 5, 2) & "/" & Right(strBirthDay, 2) & "/" & Left(strBirthDay, 4))
        datBirthDay = CDate(strBirthDay)
        booSubtractYear = False
        lngCurrentYear = Year(Date)
        lngCurrentMonth = Month(Date)
        lngCurrentDay = Day(Date)
        lngYear = Year(datBirthDay)
        lngMonth = Month(datBirthDay)
        lngDay = Day(datBirthDay)
        lngAge = lngCurrentYear - lngYear
        'If the current month is not past your birth month then subtract a month
        If lngCurrentMonth < lngMonth Then
            lngAge = lngAge - 1
            booSubtractYear = True
        End If
        'If I haven't already added a year, then check to see if the day will add a year
        If lngCurrentMonth = lngMonth And Not booSubtractYear Then
            If lngCurrentDay < lngDay Then
                lngAge = lngAge - 1
            End If
        End If
        CalculateAge = lngAge
    End Function
    Create a third query that calculates the age based on the birthdate, make sure to include all other relevant fields. Then use something like:

    Public Function AgeRange(strAge As String) As String
        Dim lngAge As Long
        If strAge = "Unknown" Then
            lngAge = "-1"
            lngAge = CLng(strAge)
        End If
        Select Case lngAge
            Case -1
            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
    Finally you are going to have to have a fourth query that has the results from your total with the results of the third query to get the total on each line of the final query.

  3. #3
    Join Date
    Sep 2004
    Tampa, FL
    Thanks for the assistance!

    To answer the question yes it is age ranges being looked for. 17-25 is one of the ranges. The ages are in the DB as a number and not a birth date.

    As odd as it may sound the DB compliles data pulled from an Alpha Micro system.

  4. #4
    Join Date
    Jul 2004
    Southampton, UK

    A simple but far less elegant solution to the range question is to create a "range" table with two fields, age and range. Then populate the range table with the ages 0-150 (or whatever people live to) and the corresponding ranges. Then create a join query between your stats table and the range table.


Posting Permissions

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