Group By Age Range

[Nikki]

Ok, I've posted before about Cross Tab queries and I do believe that's
what I'll need in this case too. My brain is just having issues
wrapping around them. I've searched for a while, but I can't seem to
apply what I've read to my situation.

I have a table, we'll call it tblTable1. I have 3 fields, ID [ID],
Date of Birth [DOB}, and Date of Death [DOD].

How do I create a report that will count the number of people in
particular age ranges at death? Let's say under 20, 20-65, over 65.

I've gotten so far as to create query that calculates the age with the
DateDiff function, but that's about it.

 

[John Bartley K7AAY]

Ok, I've posted before about Cross Tab queries and I do believe that's
what I'll need in this case too. My brain is just having issues
wrapping around them. I've searched for a while, but I can't seem to
apply what I've read to my situation.

I have a table, we'll call it tblTable1. I have 3 fields, ID [ID],
Date of Birth [DOB}, and Date of Death [DOD].

How do I create a report that will count the number of people in
particular age ranges at death? Let's say under 20, 20-65, over 65.

I've gotten so far as to create query that calculates the age with the
DateDiff function, but that's about it.

How about another table which has age and a new variable, Age Group,
then a report which summarizes Teens, Grups and Seasoned.

..
..
18 Teen
19 Teen
20 Grups
21 Grups
22 Grups
..
..
..
63 Grups
64 Grups
65 Seasoned
66 Seasoned
67 Seasoned
..
..
..

 

[John W. Vinson]

Ok, I've posted before about Cross Tab queries and I do believe that's
what I'll need in this case too. My brain is just having issues
wrapping around them. I've searched for a while, but I can't seem to
apply what I've read to my situation.

I have a table, we'll call it tblTable1. I have 3 fields, ID [ID],
Date of Birth [DOB}, and Date of Death [DOD].

How do I create a report that will count the number of people in
particular age ranges at death? Let's say under 20, 20-65, over 65.

I've gotten so far as to create query that calculates the age with the
DateDiff function, but that's about it.

As an alternative to John Bartley's suggestion, you could use a calculated
field in the query:

AgeGroup: Switch(DateDiff("yyyy", [DOB], [DOD]) - IIF(Format([DOB], "mmyy") >
Format([DOD], "mmyy"), 1, 0) < 20, "Youth", Switch(DateDiff("yyyy", [DOB],
[DOD]) - IIF(Format([DOB], "mmyy") > Format([DOD], "mmyy"), 1, 0) < 65,
"Adult", True, "Senior")

John W. Vinson [MVP]

 

[Nikki]

Thank you both!

I went with second option since I wouldn't need to make a table
containing calculated data.

Thanks again!

 

[Nikki]

Thank you both!

I went with second option since I wouldn't need to make a table
containing calculated data.

Thanks again!

 

[John Bartley K7AAY]

Ok, I've posted before about Cross Tab queries and I do believe that's
what I'll need in this case too. My brain is just having issues
wrapping around them. I've searched for a while, but I can't seem to
apply what I've read to my situation.

I have a table, we'll call it tblTable1. I have 3 fields, ID [ID],
Date of Birth [DOB}, and Date of Death [DOD].

How do I create a report that will count the number of people in
particular age ranges at death? Let's say under 20, 20-65, over 65.

I've gotten so far as to create query that calculates the age with the
DateDiff function, but that's about it.

As an alternative to John Bartley's suggestion, you could use a calculated
field in the query:

AgeGroup: Switch(DateDiff("yyyy", [DOB], [DOD]) - IIF(Format([DOB], "mmyy") >
Format([DOD], "mmyy"), 1, 0) < 20, "Youth", Switch(DateDiff("yyyy", [DOB],
[DOD]) - IIF(Format([DOB], "mmyy") > Format([DOD], "mmyy"), 1, 0) < 65,
"Adult", True, "Senior")

John W. Vinson [MVP]

Much more elegant than my hack. Thank you!

 

[John W. Vinson]

As an alternative to John Bartley's suggestion, you could use a calculated
field in the query:

AgeGroup: Switch(DateDiff("yyyy", [DOB], [DOD]) - IIF(Format([DOB], "mmyy") >
Format([DOD], "mmyy"), 1, 0) < 20, "Youth", Switch(DateDiff("yyyy", [DOB],
[DOD]) - IIF(Format([DOB], "mmyy") > Format([DOD], "mmyy"), 1, 0) < 65,
"Adult", True, "Senior")

John W. Vinson [MVP]

Much more elegant than my hack. Thank you!

Sort of depends... your table-driven approach is actually much easier to
maintain and more flexible, as you can simply change the data in the range
table rather than needing to go into the query and tweak code. Horses for
courses!