I have a large database with values that include an "Age" Field. I want to group the data by age in increments of 5 such as 16-20, 21-25, 26-30...etc etc. Can someone tell me if there is an easy way to set up a query to do this, so that I don't have to keep changing the criteria fields? Thanks!
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Grouping By Age Query 1
- Thread starter cdogstu99
- Start date
earthandfire
Programmer
You could use a Select Case statement:
Select Case Age
Case Is < 16
do whatever
Case 16 To 20
do something else
Case 21 To 25
..
..
..
Case Else 'if needed
End Select
Hope this helps.
Select Case Age
Case Is < 16
do whatever
Case 16 To 20
do something else
Case 21 To 25
..
..
..
Case Else 'if needed
End Select
Hope this helps.
Something Like this ?
SELECT Partition([Age field],16,90,5) AS AgeRange, Count(*) AS CountOfAge
FROM yourTable
GROUP BY Partition([Age field],16,90,5);
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
SELECT Partition([Age field],16,90,5) AS AgeRange, Count(*) AS CountOfAge
FROM yourTable
GROUP BY Partition([Age field],16,90,5);
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
-
1
- #5
MichaelRed
Programmer
but ... but ... but ...
why doesn't someone ask WHY anyone would use AGE?
In particuylar a " ... LARGE ... " database more-or-less assures that the AGE is not current, so grouping seems to be at best skewed to the younger?
Why doesn't someone mention that it is poor practice to store the value(s) which (can | DO) change?
... sputter ... stumble ... bang head ...
MichaelRed
why doesn't someone ask WHY anyone would use AGE?
In particuylar a " ... LARGE ... " database more-or-less assures that the AGE is not current, so grouping seems to be at best skewed to the younger?
Why doesn't someone mention that it is poor practice to store the value(s) which (can | DO) change?
... sputter ... stumble ... bang head ...
MichaelRed
Awe Michael... get over it. Just run an update query once per year on January 1st to add one to the age. We all do age at the same rate ;-)
I would go further to make a table of age ranges to handle the pointy-haired-boss that decides that every 5 years isn't good enough any more.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
I would go further to make a table of age ranges to handle the pointy-haired-boss that decides that every 5 years isn't good enough any more.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
- Thread starter
- #7
thanks guys....phv just what i wanted..but there's one thing maybe you can help me with...
I have two tables that i've joined together, and my data is insurance policies, so for each policy number there are maybe 1 2, 3 or 4 lines of data. How do i do it so that I can get a count of the policy number, and not the actual lines of data. Here's my sql which will give me the breakdown, but i just want to get a count for each category, and not line by line..
SELECT Partition([Age],16,90,5) AS AgeRange, Count(*) AS CountOfAge, Risks.[Policy Key], Count(Risks.[Policy Key]) AS [CountOfPolicy Key]
FROM Risks INNER JOIN Premiums ON Risks.[Policy Key] = Premiums.[Policy Key]
GROUP BY Partition([Age],16,90,5), Risks.[Policy Key];
I have two tables that i've joined together, and my data is insurance policies, so for each policy number there are maybe 1 2, 3 or 4 lines of data. How do i do it so that I can get a count of the policy number, and not the actual lines of data. Here's my sql which will give me the breakdown, but i just want to get a count for each category, and not line by line..
SELECT Partition([Age],16,90,5) AS AgeRange, Count(*) AS CountOfAge, Risks.[Policy Key], Count(Risks.[Policy Key]) AS [CountOfPolicy Key]
FROM Risks INNER JOIN Premiums ON Risks.[Policy Key] = Premiums.[Policy Key]
GROUP BY Partition([Age],16,90,5), Risks.[Policy Key];
MichaelRed
Programmer
Duane, your "approach" makes perfet sense ... for those where the 50% error of one year is ... perfectly acceptable!
MichaelRed
MichaelRed
Michael,
If the error was acceptable at one time, it should be acceptable at any future date.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
If the error was acceptable at one time, it should be acceptable at any future date.
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
MichaelRed
Programmer
the point was that storing "AGE" inherently introduces 'avoidable' errors (or at least deviation. It should NOT EVER have been "Acceptable".
For no one to have mentioned this seems, to me, to more-or-less ignore a more-or-less glaring (if - - - dare I say it - - - ? more-or-less common) design fallacy. Who knows when the original "AGE" value was stored? Who keeps track of having it updated? Where went the ethic of at least pointing out an obvious problem?
Are 'we' all so into posting the 'quick fix' that there is NO thought to the fact that one on these 'students' may be calculating our pension / IRA income / Social security / etc some day?
Did you want your age recorded - but not updqated for three years, then have some one your helped with this soloution use it to deny you your benefits? Were you interested in the six month debate over your age with a the "system" over your true age as an academic exercise, or was it just for the fun of seeiong if they would actyually pay the 1.5 percent interest on the funds held for the period?
...
Sorry for the rant. Perhaps I should just quit!
MichaelRed
For no one to have mentioned this seems, to me, to more-or-less ignore a more-or-less glaring (if - - - dare I say it - - - ? more-or-less common) design fallacy. Who knows when the original "AGE" value was stored? Who keeps track of having it updated? Where went the ethic of at least pointing out an obvious problem?
Are 'we' all so into posting the 'quick fix' that there is NO thought to the fact that one on these 'students' may be calculating our pension / IRA income / Social security / etc some day?
Did you want your age recorded - but not updqated for three years, then have some one your helped with this soloution use it to deny you your benefits? Were you interested in the six month debate over your age with a the "system" over your true age as an academic exercise, or was it just for the fun of seeiong if they would actyually pay the 1.5 percent interest on the funds held for the period?
...
Sorry for the rant. Perhaps I should just quit!
MichaelRed
Michael,
I agree entirely. My flippant post were aimed only at yanking your chain a little ;-)
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
I agree entirely. My flippant post were aimed only at yanking your chain a little ;-)
Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
Hi, Michael:
You've hit on the dilemma that those of us who post with answers inevitably face: to simply answer the question that was asked; or (much harder) try to dig deeper and uncover some of the more fundamental issues. Unfortunately, OP's often don't want to hear that the basic design of their database is hosed and will argue why they *must* do it this way - I'm sure you've seen this many times!
Keep fighting the good fight. I've learned an awful lot from your posts over the last 2 or 3 years (yours too, Duane!).
Ken S.
You've hit on the dilemma that those of us who post with answers inevitably face: to simply answer the question that was asked; or (much harder) try to dig deeper and uncover some of the more fundamental issues. Unfortunately, OP's often don't want to hear that the basic design of their database is hosed and will argue why they *must* do it this way - I'm sure you've seen this many times!
Keep fighting the good fight. I've learned an awful lot from your posts over the last 2 or 3 years (yours too, Duane!).
Ken S.
great thread
the best answers, of course, do both: answer the question that was asked, as we;ll as point out why it's wrong to go down that path
that said, don't ever store age, store birthdates
rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)
the best answers, of course, do both: answer the question that was asked, as we;ll as point out why it's wrong to go down that path
that said, don't ever store age, store birthdates
rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)
- Status
Similar threads
- Question
- Locked
- Question