By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 2,334 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Age Group Query

P: n/a
I'm working on a DB for a local 5K race. I want to make the DB as
flexible as possible and to avoid hard coding anything that I don't
need to so that we can reuse this for other races. As such, I can't
figure out how to determine the racers age group even though it seems
like it should be pretty easy. Below are some of the fields and
example data in two tables:

tblRacers
IdRacers 1 2
FirstName Bill Bob
LastName Smith Jones
AgeOnRaceDay 33 21

tblAgeGroups
IdAgeGroups 1 2
Letter A B
MinAge 20 29
MaxAge 30 39

Of course, the results should indicate that Bill Smith is in the 'B'
age group and that Bob Jones is in the 'A' age group.

I know that I can write a function to determine the appropriate Age
Group Letter for the racers but I'd like to do this in a query. Any
ideas?
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
How about a person who is 29 or 30; what age group is he in?

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Gordon White" <go**********@earthlink.net> wrote in message
news:d2**************************@posting.google.c om...
I'm working on a DB for a local 5K race. I want to make the DB as
flexible as possible and to avoid hard coding anything that I don't
need to so that we can reuse this for other races. As such, I can't
figure out how to determine the racers age group even though it seems
like it should be pretty easy. Below are some of the fields and
example data in two tables:

tblRacers
IdRacers 1 2
FirstName Bill Bob
LastName Smith Jones
AgeOnRaceDay 33 21

tblAgeGroups
IdAgeGroups 1 2
Letter A B
MinAge 20 29
MaxAge 30 39

Of course, the results should indicate that Bill Smith is in the 'B'
age group and that Bob Jones is in the 'A' age group.

I know that I can write a function to determine the appropriate Age
Group Letter for the racers but I'd like to do this in a query. Any
ideas?

Nov 13 '05 #2

P: n/a
"Gordon White" <go**********@earthlink.net> wrote in message
news:d2**************************@posting.google.c om...
I'm working on a DB for a local 5K race. I want to make the DB as
flexible as possible and to avoid hard coding anything that I don't
need to so that we can reuse this for other races. As such, I can't
figure out how to determine the racers age group even though it seems
like it should be pretty easy. Below are some of the fields and
example data in two tables:

tblRacers
IdRacers 1 2
FirstName Bill Bob
LastName Smith Jones
AgeOnRaceDay 33 21

tblAgeGroups
IdAgeGroups 1 2
Letter A B
MinAge 20 29
MaxAge 30 39

select r.FirstName, a.Letter
from tblRacers as r
inner join tblAgeGroups as a on r.AgeOnRaceDay >= a.MinAge
and r.AgeOnRaceDay <= a.MaxAge


Nov 13 '05 #3

P: n/a
If you want data as well as code to be reusable next year, when the race
month/day may be different, then why are you using the 'age on race day'.
This is the same issue youth sports leagues have with calculating age
groupings, and calculating off the DOB makes much more sense.
Darryl Kerkeslager

"Gordon White" <go**********@earthlink.net> wrote:
I'm working on a DB for a local 5K race. I want to make the DB as
flexible as possible and to avoid hard coding anything that I don't
need to so that we can reuse this for other races. As such, I can't
figure out how to determine the racers age group even though it seems
like it should be pretty easy. Below are some of the fields and
example data in two tables:

tblRacers
IdRacers 1 2
FirstName Bill Bob
LastName Smith Jones
AgeOnRaceDay 33 21

tblAgeGroups
IdAgeGroups 1 2
Letter A B
MinAge 20 29
MaxAge 30 39


Nov 13 '05 #4

P: n/a

John Winterbottom wrote:
"Gordon White" <go**********@earthlink.net> wrote in message
news:d2**************************@posting.google.c om...
tblRacers
IdRacers 1 2
FirstName Bill Bob
LastName Smith Jones
AgeOnRaceDay 33 21

tblAgeGroups
IdAgeGroups 1 2
Letter A B
MinAge 20 29
MaxAge 30 39

select r.FirstName, a.Letter
from tblRacers as r
inner join tblAgeGroups as a on r.AgeOnRaceDay >= a.MinAge
and r.AgeOnRaceDay <= a.MaxAge


I developed a database for a client that requires a lot of age
bracketing, with different brakes on the brackets for different
queries. The way I dealt with this was to make an age bracket table.
One field holds Age, I've made 121 rows, from 0 to 120. Then I sets of
columns, in pairs, the first one is basically a sort order for the
bracket and the second one is a label (one of the sets is Infant,
Toddler, etc, so I can't sort on the labels). Because they have a group
of clients they work with over a series of years, they have DOB data,
and I have a function that calculates age and I link from that
calculated age to the age in my tblAgeBracket.

Jeremy

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.