Group a range of records, then count

[Tammy]

Hi,

I've tried looking this one up, but it seems everyone is familiar with
Select statements except me. I'm hoping there is a way to create this query
by using the Query Design View and the Totals button (which I have a better
understanding of).

I have created a query with a calculation that figures out the age of a
person when they started their first job. (I took the start year of the job
and subtracted it from the birth date year.)

What I am attempting to do now is create groups that indicates how many
people were between:
20-29
30-39
40-49
50-59 etc.

How can I get the data grouped by those age *ranges*, and then count the
number of records in each age group?

Thanks for any help with this. I feel I'm so close, but just can't figure
out how to group by the age range.

I don't think it matters in this case, but I'm using Access 2007.

Thank you!!

 

[John Spencer (MVP)]

One method is to use a calculated field - Integer divide the calculated age
and group by that.

Field: AgeDecade: 10 * ((Year(JobStart)-Year(Dob)) \10)

You do realize that the age calculation is not totally accurate -
Job Start 1/1/2007
DOB: 12/31/1957
result is 50, but the age when job started was really 49

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tammy]

Hi John,

Thank you very much for responding to my post.

Thanks for asking about the accuracy of the age field - we are only tracking
birth *year* and start date *year*. This is a number field.

I am confused by your suggested formula, and must be doing something wrong.
Here is what I entered (using my fieldnames):

AgeDecade: 10*(([minofstart date]-[date of birth])/10)

FYI - I took out the "Year" function - I did try it, but the results were
incorrect - I'm assuming you added that to your formula, assuming we were
using a full date for our birth dates and start dates.

When I use the above formula, it is not breaking down into decade groups -
for example my results show all the age 25 people, all the age 26 people, age
27 people etc. - these are listed as individual records, not combined.

So...am I doing something wrong in the formula? Sorry for just not "getting"
it.

Thanks, again, I really appreciate your time.

 

[John Spencer]

You are not using integer division. Use \ not / to perform the division

For instance
22/10 is 2.2
22\10 is 2
26\2 is 2
Field: AgeDecade: 10 * ((Year(JobStart)-Year(Dob)) \10)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John,

Thank you very much for responding to my post.

Thanks for asking about the accuracy of the age field - we are only tracking
birth *year* and start date *year*. This is a number field.

I am confused by your suggested formula, and must be doing something wrong.
Here is what I entered (using my fieldnames):

AgeDecade: 10*(([minofstart date]-[date of birth])/10)

FYI - I took out the "Year" function - I did try it, but the results were
incorrect - I'm assuming you added that to your formula, assuming we were
using a full date for our birth dates and start dates.

When I use the above formula, it is not breaking down into decade groups -
for example my results show all the age 25 people, all the age 26 people, age
27 people etc. - these are listed as individual records, not combined.

So...am I doing something wrong in the formula? Sorry for just not "getting"
it.

Thanks, again, I really appreciate your time.

One method is to use a calculated field - Integer divide the calculated age
and group by that.

Field: AgeDecade: 10 * ((Year(JobStart)-Year(Dob)) \10)

You do realize that the age calculation is not totally accurate -
Job Start 1/1/2007
DOB: 12/31/1957
result is 50, but the age when job started was really 49

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tammy]

Thank you, John - you are right, I wasn't using integer division. I didn't
know such a thing existed - and, did make the mistake of assuming you just
used the wrong slash in the formula. I did not have to use the YEAR function
because we were not tracking full dates, only years. So, in case someone else
is reading this post, here is the formula that worked for me:

AgeDecade: 10*(([start date]-[date of birth])\10)

Thanks soooo much for you help with this, John!

You are not using integer division. Use \ not / to perform the division

For instance
22/10 is 2.2
22\10 is 2
26\2 is 2
Field: AgeDecade: 10 * ((Year(JobStart)-Year(Dob)) \10)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Hi John,

Thank you very much for responding to my post.

Thanks for asking about the accuracy of the age field - we are only tracking
birth *year* and start date *year*. This is a number field.

I am confused by your suggested formula, and must be doing something wrong.
Here is what I entered (using my fieldnames):

AgeDecade: 10*(([minofstart date]-[date of birth])/10)

FYI - I took out the "Year" function - I did try it, but the results were
incorrect - I'm assuming you added that to your formula, assuming we were
using a full date for our birth dates and start dates.

When I use the above formula, it is not breaking down into decade groups -
for example my results show all the age 25 people, all the age 26 people, age
27 people etc. - these are listed as individual records, not combined.

So...am I doing something wrong in the formula? Sorry for just not "getting"
it.

Thanks, again, I really appreciate your time.

One method is to use a calculated field - Integer divide the calculated age
and group by that.

Field: AgeDecade: 10 * ((Year(JobStart)-Year(Dob)) \10)

You do realize that the age calculation is not totally accurate -
Job Start 1/1/2007
DOB: 12/31/1957
result is 50, but the age when job started was really 49

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Tammy wrote:
Hi,

I've tried looking this one up, but it seems everyone is familiar with
Select statements except me. I'm hoping there is a way to create this query
by using the Query Design View and the Totals button (which I have a better
understanding of).

I have created a query with a calculation that figures out the age of a
person when they started their first job. (I took the start year of the job
and subtracted it from the birth date year.)

What I am attempting to do now is create groups that indicates how many
people were between:
20-29
30-39
40-49
50-59 etc.

How can I get the data grouped by those age *ranges*, and then count the
number of records in each age group?

Thanks for any help with this. I feel I'm so close, but just can't figure
out how to group by the age range.

I don't think it matters in this case, but I'm using Access 2007.

Thank you!!