Grouping age group within a query

[chedd via AccessMonster.com]

Hi i have a table with just date of births, but do not show the ages.

I designed a query to show the ages from the table with the expression Int
(DateDiff("d",[DOB],Now())/365.25).

Now what i would like to do within the same qurery is group these into less
than20, 21-30, 31-40, 41-50,51-60, greater than 60.

Any ideas as you help is most appreciated.

Chedd

 

[RonaldoOneNil]

Assuming your expression is called Age in the query, then create another
expression column in the query called Band like this.

Band: IIf([Age]<21,"20 and Under",IIf([Age]>20 And [Age]<31,"21 to
30",IIf([Age]>30 And [Age]<41,"31 to 40",IIf([Age]>40 And [Age]<51,"41 to
50",IIf([Age]>50 And [Age]<61,"51 to 60","Over 60")))))

 

[Duane Hookom]

I would use a more accurate date calculation such as those found at
http://www.mvps.org/access/datetime/date0001.htm.

I would not create a huge nested IIf() expression. If you want to create
"buckets" of ages, you should create a table of age groups with FromAge,
ToAge, and AgeGroup. You can then allow easy maintenance of age groups
without touching your expressions.

 

[Dale_Fye via AccessMonster.com]

1. Your method at calculating age is somewhat flawed, at least if what you
want to do is determine the number of years old an individual is on a
particular date. Instead, I recommend:

datediff("yyyy", dtDate, now()) + (Format(dtDate, "mmdd") > format(now(),
"mmdd"))

2. Your age bands (as you describe them) leave out those that are 20. My
guess is that you want less than or equal to 20.

3. I would create a separate table (tbl_AgeBands) with a structure similar
to:

AgeBand LowerLimit UpperLimit
<= 20 -1 20
21-30 21 30
31-40 31 40
41-50 41 50
51-60 51 60

60 61 120

Then, you can create a query that looks something like:

SELECT [SomeName],
datediff("yyyy", [DOB], now()) + (Format([DOB], "mmdd") >
format(now(), "mmdd")) as Age,
AgeBand
FROM yourTable, tbl_AgeBands
WHERE datediff("yyyy", [DOB], now()) + (Format([DOB], "mmdd") > format(now(),
"mmdd")) >= LowerLimit
AND datediff("yyyy", [DOB], now()) + (Format([DOB], "mmdd") > format(now(),
"mmdd")) <= UpperLimit

HTH
Dale

Hi i have a table with just date of births, but do not show the ages.

I designed a query to show the ages from the table with the expression Int
(DateDiff("d",[DOB],Now())/365.25).

Now what i would like to do within the same qurery is group these into less
than20, 21-30, 31-40, 41-50,51-60, greater than 60.

Any ideas as you help is most appreciated.

Chedd

 

[MGFoster]

Hi i have a table with just date of births, but do not show the ages.

I designed a query to show the ages from the table with the expression Int
(DateDiff("d",[DOB],Now())/365.25).

Now what i would like to do within the same qurery is group these into less
than20, 21-30, 31-40, 41-50,51-60, greater than 60.

Any ideas as you help is most appreciated.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try the Partition() function. Syntax:

Partition(number, start, stop, interval)

number is the Age.
start is the lowest number
stop is the highest number
interval is the range of numbers to partition on

Example:

Partition(15,21,60,10) -> :20 - which means <= 20
Partition(25,21,60,10) -> 21:30
Partition(55,21,60,10) -> 51:60
Partition(61,21,60,10) -> 61: - which means >= 61

The Partition() function only uses Integer numbers.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSj/ODYechKqOuFEgEQLsUwCgodpbbV03SpderWRvH59npGCJRKoAoO5z
H1tWOsmVTJv4vgnlNPXrmDAC
=55nb
-----END PGP SIGNATURE-----