424,851 Members | 1,172 Online
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

# Calculating age from DOB and ranging it into 4 age groups

 P: 3 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. Apr 25 '10 #1

@ruthii
There are several ways to approach this problem; I felt that this was the easiest:
1. Create a Public Function named fCalcAgegroup(). This Function will accept a DOB Argument, then calculate the appropriate Age Group based on your specifications.
Expand|Select|Wrap|Line Numbers
1. Public Function fCalcAgeGroup(dteDOB As Date) As Variant
2. Dim intYrsDiff As Variant
3.
4. intYrsDiff = DateDiff("yyyy", dteDOB, Now)
5.
6. If Date < DateSerial(Year(Now), Month(dteDOB), Day(dteDOB)) Then
7.   intYrsDiff = intYrsDiff - 1
8. End If
9.
10. If intYrsDiff >= 0 And intYrsDiff < 2 Then
11.   fCalcAgeGroup = 1     'Group 1
12. ElseIf intYrsDiff >= 2 And intYrsDiff < 3 Then
13.   fCalcAgeGroup = 2     'Group 2
14. ElseIf intYrsDiff >= 3 And intYrsDiff < 5 Then
15.   fCalcAgeGroup = 3     'Group 3
16. ElseIf intYrsDiff >= 5 And intYrsDiff <= 11 Then
17.   fCalcAgeGroup = 4     'Group 4
18. Else    '> 11 years old, return NULL
19.   fCalcAgeGroup = Null    'Out of Range
20. End If
21. End Function
2. 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.
3. 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.
Expand|Select|Wrap|Line Numbers
1. SELECT [Childrens Details].First, [Childrens Details].Last, [Childrens Details].DOB,
2.  fCalcAgeGroup([DOB]) AS [Age Group], Format(DLookUp("[Price]","tblPricing","[GroupID] = " & [Age Group]),"\$#.00") AS Price FROM [Childrens Details];
4. Execute qryAgeGroups to View the desired results.
5. 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.

4 Replies

Expert 5K+
P: 8,616
@ruthii
There are several ways to approach this problem; I felt that this was the easiest:
1. Create a Public Function named fCalcAgegroup(). This Function will accept a DOB Argument, then calculate the appropriate Age Group based on your specifications.
Expand|Select|Wrap|Line Numbers
1. Public Function fCalcAgeGroup(dteDOB As Date) As Variant
2. Dim intYrsDiff As Variant
3.
4. intYrsDiff = DateDiff("yyyy", dteDOB, Now)
5.
6. If Date < DateSerial(Year(Now), Month(dteDOB), Day(dteDOB)) Then
7.   intYrsDiff = intYrsDiff - 1
8. End If
9.
10. If intYrsDiff >= 0 And intYrsDiff < 2 Then
11.   fCalcAgeGroup = 1     'Group 1
12. ElseIf intYrsDiff >= 2 And intYrsDiff < 3 Then
13.   fCalcAgeGroup = 2     'Group 2
14. ElseIf intYrsDiff >= 3 And intYrsDiff < 5 Then
15.   fCalcAgeGroup = 3     'Group 3
16. ElseIf intYrsDiff >= 5 And intYrsDiff <= 11 Then
17.   fCalcAgeGroup = 4     'Group 4
18. Else    '> 11 years old, return NULL
19.   fCalcAgeGroup = Null    'Out of Range
20. End If
21. End Function
2. 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.
3. 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.
Expand|Select|Wrap|Line Numbers
1. SELECT [Childrens Details].First, [Childrens Details].Last, [Childrens Details].DOB,
2.  fCalcAgeGroup([DOB]) AS [Age Group], Format(DLookUp("[Price]","tblPricing","[GroupID] = " & [Age Group]),"\$#.00") AS Price FROM [Childrens Details];
4. Execute qryAgeGroups to View the desired results.
5. 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, 82 views)
Apr 25 '10 #2

 P: 3 @ADezii Thank you for your reply. By function do you mean a query?... Because I'm using Access 2003. Apr 25 '10 #3

 Expert 5K+ P: 8,616 @ruthii 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. Apr 25 '10 #4

 P: 3 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 Apr 28 '10 #5