To make Age Range Query to select men between these ages

[mass2051]

I have data in which age of all men is entered. Now I need a query to find the men who are in the ages between 15-20, 21-25, 26-30, above 30, above 40. How to make qry for this. Pl help. The age between may be fixed as shown or can make two text boxes to type ages and click ok to find.

[Rabbit]

You can use the WHERE clause to filter on whatever range you want to use.

For example:

SELECT *
FROM someTable
WHERE Age BETWEEN 15 AND 20

[NeoPa]

Before you go any further though, you should consider the idea of storing ages at all. Dates of Birth will always reflect the age, whereas Ages will only ever be correct until their next birthdays.

To test for ages in such a scenario :

SELECT *
FROM   [YourTable]
WHERE  ([DateOfBirth] Between DateAdd('yyyy',-20, Date()) And DateAdd('yyyy',-15, Date()))

[ADezii]

There is a little known and rarely used Function (Partition()) that can greatly simplify matters for you. Suppose you have a Table named tblAges with a Field named [Age]. You wish to obtain the Aggregate Totals for all Age Groups within the Ages of 15 to 100 at 5 Year Intervals. The SQL and Test results are as follows:

SELECT DISTINCTROW Partition([Age],15,100,6) AS Age_Range, Count(tblAges.Age) AS Range_Count
FROM tblAges
GROUP BY Partition([Age],15,100,6);

Age_Range    Range_Count
11: 15            2
16: 20            3
21: 25            7
31: 35            8
51: 55            4
56: 60            5
76: 80            2
81: 85            1

PS (NeoPa) The Partition() Function from another post is helpful to include in Best Answer.

[NeoPa]

Excellent idea ADezii. I'd still recommend the use of [DoB] data rather than [Age] though. Partition will handle either way perfectly well.

[ADezii]

An Article exists in Bytes that will provide expanded information on the Partition() Function if anyone is interested. Sorry for going Off-Topic a little, but I feel that it is related to this Thread.
http://bytes.com/topic/access/insigh...ition-function

[NeoPa]

Not at all. Very relevant I'd say. Good for you.

[ADezii]

I prefer to use more accurate Age Calculation Logic as opposed to Birth Days, as in:

Function fCalculateAge(varDOB As Variant, Optional varAsOfThisDate As Variant) As Variant
'Purpose:   Return the Age in years.
'Arguments: varDOB = Date Of Birth
'varAsOfThisDate = the date to calculate the age at, or today if missing.
 
'The DateDiff() Function simply subtracts the Year parts of the Dates, without
'reference to the Month or Day. This means we need to subtract one if the person
'has not has their birthday this year. This can be handled by a Boolean Expression
'that returns either -1 or 0 depending on whether or not an individual's
'birthday occurred within the Year.
 
'Return:    Whole number of years.
Dim dteDOB As Date
Dim dteAsOf As Date
Dim dteBDay As Date     'Birthday in the year of calculation.
 
fCalculateAge = Null    'Initialize to Null
 
'Validate Parameters
If IsDate(varDOB) Then
  dteDOB = varDOB
 
  If Not IsDate(varAsOfThisDate) Then  'Date to calculate age from.
    dteAsOf = Date      'Use the Current Date
  Else
    dteAsOf = varAsOfThisDate       'Valid As Of Date
  End If
 
  If dteAsOf >= dteDOB Then      'Calculate only if it's after person was born.
    dteBDay = DateSerial(Year(dteAsOf), Month(dteDOB), Day(dteDOB))
      fCalculateAge = DateDiff("yyyy", dteDOB, dteAsOf) + (dteBDay > dteAsOf)
  End If
End If
End Function

[NeoPa]

ADezii:
I prefer to use more accurate Age Calculation Logic as opposed to Birth Days, as in:

Using DateDiff() can certainly lead you into muddy waters ADezii, yet that isn't the case with DateAdd() (as used in post #3). Although the actual values used may want to be tweaked if that were the approach used, rather than using Partition() which would be my favourite, as the example shown cuts off after the 20th birthday, the accuracy is fine.

To produce an accurate [Age] value though, you would not want to rely solely on DateDiff(), clearly.

Rather than a bespoke function though, I'd suggest slightly more complicated SQL as in :

SELECT DateDiff('yyyy',[DateOfBirth],Date())-IIf(DateAdd('yyyy',DateDiff('yyyy',[DateOfBirth],Date()),[DateOfBirth])>Date(),1,0) AS [Age]

This will return a fully accurate [Age] based on the [DateOfBirth] value.

[ADezii]

Bravo! A much cleaner and more efficient approach. Nice job, NeoPa.