grouping data by age

[justme43]

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.

[nico5038]

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:

SELECT .....
FROM tblAge, tblAgeGroup
WHERE [Age] between [AgeFrom] and [AgeTo] 
 

It's up to you to make the table covering all ages from your tblAge, but I guess that's obvious.

Nic;o)

[justme43]

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

[nico5038]

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)

[justme43]

thanking you I will try that

[NeoPa]

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.

[ADezii]

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

[justme43]

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

[nico5038]

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)

[justme43]

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

[nico5038]

Hmm, can you post the SQL text of both queries so we can have a look ?

Nic;o)

[justme43]

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.

[nico5038]

OK, lets start with the qryAgeGroup. Try one of the methods and post the result here so we can have a look.

Nic;o)

[justme43]

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

[justme43]

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

[justme43]

Could it have something to do with my relationships

[nico5038]

A SELECT query can't block the database.
Can you post the SQL text here ?

Nic;o)

[NeoPa]

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.

[justme43]

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

[justme43]

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

SELECT DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate], 
 >"mmdd") > Format(Date(), "mmdd"), 1, 0)\10 AS [Decade], 
Count(MBRS.MbrBirthdate) AS CountByDecade 
FROM MBRS 
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
GROUP BY DateDiff("yyyy", [MbrBirthdate], Date()) - IIF(Format([MbrBirthdate], 
 >"mmdd") > Format(Date(), "mmdd"), 1, 0)\10

If you want to use your formula for age (which is a bit less accurate then)

SELECT ((DATE()-[MbrBirthdate])/365.25)\10 AS [Decade], 
Count(MBRS.MbrBirthdate) AS CountDecade 
FROM MBRS 
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
GROUP BY ((DATE()-[MbrBirthdate])/365.25)\10 
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

SELECT tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin 
Count(MBRS.MbrBirthdate) AS CountOfMbrBirthdate 
FROM MBRS INNER JOIN AgeGroup 
ON ((Now()-MBRS.[MbrBirthdate])/365) >= tblAgeGroup.AgeMin 
AND  ((Now()-MBRS.[MbrBirthdate])/365) < tblAgeGroup.AgeMax 
WHERE MBRS.Memtype<>"comp" AND MBRS.ExpDate>Date() 
GROUP BY tblAgeGroup.AgeGroup, tblAgeGroup.AgeMin 
ORDER BY tblAgeGroup.AgeMin DESC;

[nico5038]

I would use an Age function (stored in a module) like:

Function Age(Bdate, DateToday) As Integer
' Returns the Age in years between 2 dates
' Doesn't handle negative date ranges i.e. Bdate > DateToday
 
    If Month(DateToday) < Month(Bdate) Or (Month(DateToday) = _
                Month(Bdate) And Day(DateToday) < Day(Bdate)) Then
            Age = Year(DateToday) - Year(Bdate) - 1
    Else
            Age = Year(DateToday) - Year(Bdate)
    End If
End Function

From http://www.mvps.org/access/datetime/date0001.htm (good site with many good samples and info!)

Now your query can use:

SELECT tblAgeGroup.AgeGroup, Count(*) AS MembersInGroup
FROM MBRS, AgeGroup
WHERE Age(MBRS.[MbrBirthdate] between tblAgeGroup.AgeMin AND tblAgeGroup.AgeMax
AND MBRS.Memtype<>"comp" 
AND MBRS.ExpDate>Date()
GROUP BY tblAgeGroup.AgeGroup
ORDER BY 1 DESC;

When the MBRS.ExpDate can be empty, an additional IsNull test should be added...

Getting the idea ?

Nic;o)

[justme43]

this has worked thanking you very much for your time and patience.

[nico5038]

Glad I could help. Success with your application "Down under" :-)

Nic;o)

[NeoPa]

@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.