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/insights/708740-partition-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