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

grouping data by age

P: 18
Hi
I am having trouble getting my head around how to go about group ages
I have a querytable which holds the information that I need that is the age field What I want to do is group this by ages groups eg under 19, 20 to 29, 30 to 39 etc.
I then need to put this information into a crosstab query to do the count
I have tried to do this several ways but am at a loss as to why I am not winning
I would appreciate any help that you can give.
If helping please state exactly where I enter the information as I think this is where I am going wrong.
Jan 18 '10 #1

✓ answered by nico5038

I would use an Age function (stored in a module) like:
Expand|Select|Wrap|Line Numbers
  1. Function Age(Bdate, DateToday) As Integer
  2. ' Returns the Age in years between 2 dates
  3. ' Doesn't handle negative date ranges i.e. Bdate > DateToday
  4.  
  5.     If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
  6.                 Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
  7.             Age = Year(DateToday) - Year(Bdate) - 1
  8.     Else
  9.             Age = Year(DateToday) - Year(Bdate)
  10.     End If
  11. End Function
From http://www.mvps.org/access/datetime/date0001.htm (good site with many good samples and info!)

Now your query can use:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAgeGroup.AgeGroup, Count(*) AS MembersInGroup
  2. FROM MBRS, AgeGroup
  3. WHERE Age(MBRS.[MbrBirthdate] between tblAgeGroup.AgeMin AND tblAgeGroup.AgeMax
  4. AND MBRS.Memtype<>"comp" 
  5. AND MBRS.ExpDate>Date()
  6. GROUP BY tblAgeGroup.AgeGroup
  7. ORDER BY 1 DESC;
When the MBRS.ExpDate can be empty, an additional IsNull test should be added...

Getting the idea ?

Nic;o)

Share this Question
Share on Google+
23 Replies


nico5038
Expert 2.5K+
P: 3,072
Multiple ways of achieving this, but the most flexible is to create a table with [AgeFrom] and [AgeTo] and [AgeGroup].
Use these fields in the query to join with the original table with [Age] like:
Expand|Select|Wrap|Line Numbers
  1. SELECT .....
  2. FROM tblAge, tblAgeGroup
  3. WHERE [Age] between [AgeFrom] and [AgeTo] 
  4.  
It's up to you to make the table covering all ages from your tblAge, but I guess that's obvious.

Nic;o)
Jan 18 '10 #2

P: 18
is there away to put an expression or function in that will group even in excel you can group with out making new tables I would perfer to have a function or something like
Jan 18 '10 #3

nico5038
Expert 2.5K+
P: 3,072
Press the "E" (Sigma) looking button to create a Group By query for totalling field(s). Access will add in query design mode a row for the aggregation functions like Sum, Count, Max, etc.
Also check the help file for the Group By queries when you haven´t used them before.

Nic;o)
Jan 18 '10 #4

P: 18
thanking you I will try that
Jan 18 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
Can I suggest that, though the table approach may seem a little incongruous, or maybe just overkill, that it is actually a perfect database solution. I recommend you reconsider. Such things separate the men from the boys in database work.
Jan 18 '10 #6

ADezii
Expert 5K+
P: 8,638
There is a little known, but very useful Function, that deals specifically with this issue. It is called the Partition() Function and it can be incorporated directly into an SQL Statement with no need for any external Tables. All you need is to pass to the Function a Numeric Field, a Start, Stop, and Interval Value. Partition(0 will total the number of results for specified Ranges. I wrote a Tip on this very Topic a little time ago, and I will not go into detail relating to it since the Link to the Tip says it all.
http://bytes.com/topic/access/insigh...ition-function
Jan 19 '10 #7

P: 18
I do so thank both ADezil & NeoPa for your advise I will do some more reading I really need this to work To date everyone has had their own DB and they have been hand writing their reports About 6 months ago I did an excel program for the reports which works well but they still have to double handle the information. I have used Access in the past but not for this type of reporting and I must say I am at a loss at the moment. So I do appreciate all the support and suggestions you are making I do so hope I am not to dumb and old to figure it out thanks again Just Me
Jan 19 '10 #8

nico5038
Expert 2.5K+
P: 3,072
First create a query to get the agegroup available in a column.
Next create a crosstable query using the previously created query.

Let me know when you get stuck and where you get stuck.

Nic;o)
Jan 19 '10 #9

P: 18
I can get the first Query but the cross table query say the are to many rows and if I use columns to many columns. I was trying to pull data from different tables but that did not work so I did a querytable I think they are referred to as action queries i this It kept saying that something was opened in exclusive use.
I have most of the other things I need for my report they are Nationality, Benifits Employment Postcode and gender. but I just cant seem to get the age one. The others I had to do in seperate crosstab queries and I am hoping that the report will pull it all together. Maybe I should take a weeks break and come back to it. I am just not winning at all. And then to day the print report form I had set up that I use all the time stopped working and I cant see where. its just a simple date from date to with buttons from week month year but for some reason it just does not want to work now. one other thing can I lock the data on a form but have a button that allows editing and new entries. I have never worried about this function before I thought it was a good idea but it lock the new record also I just a little worried about some of the staff that I only want them to look up. They are mostly little old ladies that think they know everything and they dont listen to what you are saying there for stuff most stuff up thanks for your time I really did not expect this to be such a big deal
Jan 19 '10 #10

nico5038
Expert 2.5K+
P: 3,072
Hmm, can you post the SQL text of both queries so we can have a look ?

Nic;o)
Jan 19 '10 #11

P: 18
Last night I deleted everything to do with the age queries and I was going to start fresh again today. If this does not work I will gladly send it to you. I am so grateful you are interested. I dont even have a sole here that has any idea of what I am talking about.
Jan 19 '10 #12

nico5038
Expert 2.5K+
P: 3,072
OK, lets start with the qryAgeGroup. Try one of the methods and post the result here so we can have a look.

Nic;o)
Jan 19 '10 #13

P: 18
Ok i can make a query with the details from Client Detail and Assistance Tables The details are From Client Details [Clientno] and [age] From Assistance [Date] This works fine it gives me all the info I need. But when I try to do the other query adding the Age Group stuff I get than the file in opened in exclusive use I can not see why
Jan 20 '10 #14

P: 18
Question I have check to make sure the data base is not share I have also checked that the startup is allowing changes Is there something that I dont know about in regards to exclusive use. I am now unable to make a new form its say that I dont have exclusive us and my changes may not be saved
I think I am losing the plot here. I have been checking the integerity of the data base all the way have I missed something or have I just done the wrong thing somewhere sorry to be a pain
Jan 20 '10 #15

P: 18
Could it have something to do with my relationships
Jan 20 '10 #16

nico5038
Expert 2.5K+
P: 3,072
A SELECT query can't block the database.
Can you post the SQL text here ?

Nic;o)
Jan 20 '10 #17

NeoPa
Expert Mod 15k+
P: 31,494
May I point out that unless you take some more care over your posts there is a danger that many of our experts will ignore your posts in future due to the obvious lack of care taken before posting, even to formulate your thoughts into readable English. I can tell you from my own perspective that I don't appreciate having to read most of your posts 3 times just to make some kind of sense. I suggest, if you're interested in receiving help in future, you take care to avoid giving the impression you value your own time so much more highly than the time you waste of our experts.

-Administrator.
Jan 21 '10 #18

P: 18
I am trying not to waste your time. I have had some pressing work that has to be completed and that is why I have not posted anything. I have also been doing some reading trying to work out which is the best way to work out my problem as my data base will not let me do anything with it at the moment.
I have tried to export my tables to another data file but i am still having the same problem. Tonight I have spent on this. I am extremely sorry if I have upset you I am trying very hard to do my best. I really do appreciate all the time you have given me and my time is not more valuable than yours I regret giving that impression. Can you please give me some time to figure out what I have done. and accept my apology
Jan 21 '10 #19

P: 18
In my reading today I found this I have not been able to try it out I was wondering what you thought of it
If you are trying to count by decades 0 to 9, 10 to 19, etc you can accomplish
this with just some math
Expand|Select|Wrap|Line Numbers
  1. SELECT DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate], 
  2.  >"mmdd") > Format(Date(), "mmdd"), 1, 0)\10 AS [Decade], 
  3. Count(MBRS.MbrBirthdate) AS CountByDecade 
  4. FROM MBRS 
  5. WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
  6. GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate], 
  7.  >"mmdd") > Format(Date(), "mmdd"), 1, 0)\10
If you want to use your formula for age (which is a bit less accurate then)
Expand|Select|Wrap|Line Numbers
  1. SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade], 
  2. Count(MBRS.MbrBirthdate) AS CountDecade 
  3. FROM MBRS 
  4. WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
  5. GROUP BY ((DATE()-[MbrBirthdate])/365.25)\10 
  6. ORDER BY ((DATE()-[MbrBirthdate])/365.25)\10 DESC;
If you need date ranges that are different than that you would need a table
similar to Duane's. I would modify it slightly and make ageMax the cutoff
value that is for the first group I would have 10 not 9. I would then test in
the query to make sure the Age Group was LESS than the AgeMax. Modifying your
query to use that table
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin 
  2. Count(MBRS.MbrBirthdate) AS CountOfMbrBirthdate 
  3. FROM MBRS INNER JOIN AgeGroup 
  4. ON ((Now()-MBRS.[MbrBirthdate])/365) >= tblAgeGroup.AgeMin 
  5. AND  ((Now()-MBRS.[MbrBirthdate])/365) < tblAgeGroup.AgeMax 
  6. WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
  7. GROUP BY tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin 
  8. ORDER BY tblAgeGroup.AgeMin DESC;
Jan 21 '10 #20

nico5038
Expert 2.5K+
P: 3,072
I would use an Age function (stored in a module) like:
Expand|Select|Wrap|Line Numbers
  1. Function Age(Bdate, DateToday) As Integer
  2. ' Returns the Age in years between 2 dates
  3. ' Doesn't handle negative date ranges i.e. Bdate > DateToday
  4.  
  5.     If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
  6.                 Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
  7.             Age = Year(DateToday) - Year(Bdate) - 1
  8.     Else
  9.             Age = Year(DateToday) - Year(Bdate)
  10.     End If
  11. End Function
From http://www.mvps.org/access/datetime/date0001.htm (good site with many good samples and info!)

Now your query can use:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblAgeGroup.AgeGroup, Count(*) AS MembersInGroup
  2. FROM MBRS, AgeGroup
  3. WHERE Age(MBRS.[MbrBirthdate] between tblAgeGroup.AgeMin AND tblAgeGroup.AgeMax
  4. AND MBRS.Memtype<>"comp" 
  5. AND MBRS.ExpDate>Date()
  6. GROUP BY tblAgeGroup.AgeGroup
  7. ORDER BY 1 DESC;
When the MBRS.ExpDate can be empty, an additional IsNull test should be added...

Getting the idea ?

Nic;o)
Jan 21 '10 #21

P: 18
this has worked thanking you very much for your time and patience.
Jan 22 '10 #22

nico5038
Expert 2.5K+
P: 3,072
Glad I could help. Success with your application "Down under" :-)

Nic;o)
Jan 22 '10 #23

NeoPa
Expert Mod 15k+
P: 31,494
@JustMe43
I'm not upset. I'm merely trying to advise you how your posts can be seen from this side of the fence. Your posts have not been breaking any rules as such, so my moderator hat (& truncheon) were not needed.

If all your future posts are as free of confusion and typos as this one was, then I see no reason to worry.

I'm also pleased to see that you have received some help in my absence (which was related to me rather than trying to avoid this particular thread) over the last number of days. I will arrange to have Nico's helpful post set as the Best Answer to reflect this.
Jan 24 '10 #24

Post your reply

Sign in to post your reply or Sign up for a free account.