automate date of birth to age groups in calendar year

[sirnickettynox]

I have a list of club members ranging in age from 5 years to 62 years of age. They are grouped into several age groups ie. under 9's, under 12's, under 15's, under 17's, under 20's, and Seniors. The question is how to automate my access db so that every time a club member reaches the next age group, ie. an 11 year old turns 12 in say 2013, he/she gets automatically slotted into the next age group. Currently I have to go through my list of members and manually change their age group when their dob hits the current year.

[Luuk]

Something like:

select Name, Population, if(Population<130000,'1',IF(Population<170000,'2','3')) 
from City 
where CountryCode='USA';

(not tested with vba/access)

[Stewart Ross]

I would suggest that you need to calculate the age of the person concerned, then group the age into the bandings you mention. If you calculate the age you should never have to manually assign groupings the way you are doing at present.

I use a custom VBA function which calculates the age of a person as at the current date.

To use the function you first need to copy the code below and place it in any public code module (you'll need to create one if you do not have any public modules at present).

Public Function fAge(ByVal TheDOB) As Integer
    Dim YearsDiff As Integer
    Dim BirthdayThisYear As Date
    If Not IsNull(TheDOB) Then
        BirthdayThisYear = DateSerial(Year(Date), Month(TheDOB), Day(TheDOB))
        YearsDiff = DateDiff("yyyy", TheDOB, BirthdayThisYear)
        If BirthdayThisYear > Date Then
            YearsDiff = YearsDiff - 1
        End If
    End If
    fAge = YearsDiff
End Function

With this (or any equivalent function) you can then refer to a person's age in any query by using a calculated field. In SQL terms this would just be:

SELECT YourFirstField, YourNextField,..., fAge([YourDOBField]) as Age, ..., FROM YourTable

NB By default Access databases do not allow VBA code to run - in Access 2007 and later you need to change the trust centre settings to allow macros to run.

-Stewart

[Rabbit]

Create an age group table with a min and max for the range. Then you can join to it on the age where it's between the min and max.

[NeoPa]

You should be aware that such links, though they work in Access, are not supported in the query design grid.

[sirnickettynox]

Thanks Guys, will dedicate a little more time in a day or two to think about this, I'm not exactly an expert on db so everything takes time. But I will get back to you soon.
Thanks again,
Noxi.

[NeoPa]

That's not a problem Noxi.

NB. I wasn't trying to put you off the idea of the more complex linking, just warning you that you shouldn't expect to be able to see the query in Design View after you've made that change. You'll have to work in SQL View after that.