Calculating age from DOB and ranging it into 4 age groups

**ADezii** · Apr 25 '10, 04:08 PM

Originally posted by ruthii

I recently started using Access to make my database for my A2 coursework, therefore Im not an expert at all! I need help DESPERATLY please!!!!

My database is about a nursery and i have 5 different tables which include :
-Children details
-Parents details
-Staff member details
-Clubs
- Timetables

The 'Club' tables consists of all the information the nursery offers the students, this includes the prices. Now different age groups have different prices.

-0-2 Years old
-2-3 Years old
-3-5 Years old
-5-11 Years old

There are also 7 different clubs so prices range. I want to be able to perform a query that works out the Age of the children from the 'DOB' field from the children's details, and then be able to place them into the correct age group prices. Some clubs offered are payed weekly and some are payed daily so that needs to also be put into consideration.

I do realise that this is quite complex (well at least for me) but i want to give it a try, if there are any other solutions feel free to say :D ....I am reallyyyy confused with this.

There are several ways to approach this problem; I felt that this was the easiest:

Create a Public Function named fCalcAgegroup() . This Function will accept a DOB Argument, then calculate the appropriate Age Group based on your specifications.

Code:

Public Function fCalcAgeGroup(dteDOB As Date) As Variant
Dim intYrsDiff As Variant

intYrsDiff = DateDiff("yyyy", dteDOB, Now)

If Date < DateSerial(Year(Now), Month(dteDOB), Day(dteDOB)) Then
  intYrsDiff = intYrsDiff - 1
End If

If intYrsDiff >= 0 And intYrsDiff < 2 Then
  fCalcAgeGroup = 1     'Group 1
ElseIf intYrsDiff >= 2 And intYrsDiff < 3 Then
  fCalcAgeGroup = 2     'Group 2
ElseIf intYrsDiff >= 3 And intYrsDiff < 5 Then
  fCalcAgeGroup = 3     'Group 3
ElseIf intYrsDiff >= 5 And intYrsDiff <= 11 Then
  fCalcAgeGroup = 4     'Group 4
Else    '> 11 years old, return NULL
  fCalcAgeGroup = Null    'Out of Range
End If
End Function

Create a Table named tblPricing which will consist of only 2 Fields:
1. [GroupID] - {BYTE} - can be either 1, 2, 3, or 4
2. [Price] - {CURRENCY} - pre-established Price for each Group.

Create the following Query which will display the First Name, Last Name, DOB, Age Group as calculated by a call to the Public Function, and Price via a DLookup() on the tblPricing for the appropriate Price.

Code:

SELECT [Childrens Details].First, [Childrens Details].Last, [Childrens Details].DOB,
 fCalcAgeGroup([DOB]) AS [Age Group], Format(DLookUp("[Price]","tblPricing","[GroupID] = " & [Age Group]),"$#.00") AS Price FROM [Childrens Details];

Execute qryAgeGroups to View the desired results.
Couldn't resist keeping you hanging on this long (LOL). Simply Download the Attachment for the Demo Database that I created for you. It will give you a much better indication of what is going on.

P.S. - For the sake of simplicity and brevity, I made the major assumption that the [DOB] Field does not contain any NULLS, and that all entries are, in fact, valid Dates.

Attached Files

Age Groups.zip (18.6 KB, 191 views)

**ruthii** · Apr 25 '10, 04:22 PM

Originally posted by ADezii

there are several ways to approach this problem, I felt that this was the easiest:

Create a Public Function named fCalcAgegroup() . This Function will accept a DOB Argument, then calculate the appropriate Age Group based on your specifications.

Code:

Public Function fCalcAgeGroup(dteDOB As Date) As Variant
Dim intYrsDiff As Variant

intYrsDiff = DateDiff("yyyy", dteDOB, Now)

If Date < DateSerial(Year(Now), Month(dteDOB), Day(dteDOB)) Then
  intYrsDiff = intYrsDiff - 1
End If

If intYrsDiff >= 0 And intYrsDiff < 2 Then
  fCalcAgeGroup = 1     'Group 1
ElseIf intYrsDiff >= 2 And intYrsDiff < 3 Then
  fCalcAgeGroup = 2     'Group 2
ElseIf intYrsDiff >= 3 And intYrsDiff < 5 Then
  fCalcAgeGroup = 3     'Group 3
ElseIf intYrsDiff >= 5 And intYrsDiff <= 11 Then
  fCalcAgeGroup = 4     'Group 4
Else    '> 11 years old, return NULL
  fCalcAgeGroup = Null    'Out of Range
End If
End Function

Create a Table named tblPricing which will consist of only 2 Fields:
1. [GroupID] - {BYTE} - can be either 1, 2, 3, or 4
2. [Price] - {CURRENCY} - pre-established Price for each Group.

Create the following Query which will display the First Name, Last Name, DOB

Code:

SELECT [Childrens Details].First, [Childrens Details].Last, [Childrens Details].DOB, Age Group as calculated by a call to the Public Function, and Price via a DLookup() on the tblPricing for the appropriate Price.
 fCalcAgeGroup([DOB]) AS [Age Group], Format(DLookUp("[Price]","tblPricing","[GroupID] = " & [Age Group]),"$#.00") AS Price
FROM [Childrens Details];

Execute qryAgeGroups to View the desired results.
Couldn't resist keeoing you hanging on this long (LOL). Simply Download the Attachment for the Demo Database that I created for you. It will give you a much better indication of what is going on.

P.S. - For the sake of simplicity and brevity, I made the major assumption thast the [DOB] Field does not contain any NULLS, and that all entries are, in fact, valid Dates.

Thank you for your reply.

By function do you mean a query?... Because I'm using Access 2003.

**ADezii** · Apr 25 '10, 04:56 PM

Originally posted by ruthii

Thank you for your reply.

By function do you mean a query?... Because I'm using Access 2003.

A Function and a Query are two different animals. If you Open Module1, you'll see the Function which is a code structure that returns a Value, If you Open the Query in Design View, you will see how the Function is called for the Age Group Field.

**ruthii** · Apr 28 '10, 10:46 AM

Thanks Luke that website was very helpful, I think I might just not do the grouping of the children's age depending on the age because i will not have enough time to finish my project and am still pretty confused.

Thanks to you all :D

Calculating age from DOB and ranging it into 4 age groups

Calculating age from DOB and ranging it into 4 age groups

Comment

Comment

Comment

Comment