Age groups from ages

[Tomyumflavour]

Hi,

How do I make a column with ages into age groups?

I know how to do it using VLOOKUP in excel, but how do you do it in access?


Thanks

 

[plog]

First, since you listed this in the Table section of the site, I have to say you don't create a column in your table to capture it. Since you are essentially calculating the age group, the correct method is to do this is in a query. Also, you say you have a field that contains the age of a person--I'll yell at you for this in the last paragraph using the same argument I just used about storing AgeGroup in a table.

In a query you can do this by 2 methods. 1. You could create a function in a module that calculates which group each age falls into and then pass that function the age of the current record like this:

AgeGroup: getAgeGroup([PersonsAge])

2. You could also set up another table that maps each age into a group. Then when you want to group ages you bring both tables in, link them and then display the AgeGroup field from that table you created.

Finally, you shouldn't store ages in a database, the prefered method is to store Date Of Birth. Age is essentially a calculation based on today's date and date of birth. When you store someone's age that has a shelf life. In a week, a month, especially in a year that value will be outdated. If you store Date of Birth and always calculate their age when you need it, you will always have their correct age without having to manually update your data.

 

[jdraw]

I agree with plog that you should NOT store Age in a table.
If you do want to put ages in a query, you might also investigate the
Partition function in Access.

There is an example here
http://bytes.com/topic/access/insights/708740-partition-function

 

[gemma-the-husky]

if the age groups you require are the same size (eg 5 year bands) the PARTITION function will do this for you, in a query.

 

[Robert-SK]

Create a new field in the query called agegroup, hoping that u already have age converted from DOB, then this must work 100% well. just copy and paste.

agegrp: Format((IIf([age] Between 0 And 0,"less 1 year",IIf([age] Between 1 And 4,"1-4 years",IIf([age] Between 5 And 14,"5-14 years",IIf([age] Between 15 And 17,"15-17 years",IIf([age] Between 18 And 24,"18-24 years",IIf([age] Between 25 And 999,"25+ years"))))))))

Enjoy
SK-256