1. Registered User
Join Date
Sep 2004
Location
Tampa, FL
Posts
520

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. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
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:

Code:
```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:

Code:
```Public Function AgeRange(strAge As String) As String

Dim lngAge As Long

If strAge = "Unknown" Then
lngAge = "-1"
Else
lngAge = CLng(strAge)
End If

Select Case lngAge

Case -1
AgeRange="Unknown"
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. Registered User
Join Date
Sep 2004
Location
Tampa, FL
Posts
520
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. Registered User
Join Date
Jul 2004
Location
Southampton, UK
Posts
368
Hi

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.

Chris

#### Posting Permissions

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