Help with query to do counts!

[Ibero]

This might be a bit difficult to explain, but i will give it my best shot. I need queries to give me the count in various fields, but with specific thresholds within the particular field, and with a criteria. So for example, I have an age field calculated. Now i created a new query to give me the "count" for how many people are between age 1-10, 11-20, 21-29, and so on. I did this by putting in coresponding criterias such as "between 1 and 10" in each field. However, because all thresholds (which are my fields) in that query are using the same calculated fields (age, from another query), only one criteria is actively working. Two or more fields using the same "age" field just returns whatever my first criteria is over and over. So if i make a field "1-10" with it's criteria, then the fields "11-20" with ITS corresponding criteria, both fields just end up giving me the count of the first criteria. Am i doing something wrong? Is there an easier way to do this? Also once i can get this working, there's another condition i need to implement, which is: count of "1-10" for "2006", all in that query. then another query for the count of the ages thresholds in 2007, and so on. Any help is greatly appreciated! Thanks.

 

[wazz]

queries in the query grid kind of "cascade" from left to right. if the first column is looking for people between 1 and 10, the rows that are returned are instantly limited to...people between 1 and 10. so, if your next field wants people between 11 and 20 you won't get any - the list is already "filtered" to exclude them. this concept will apply to most of your question(s) and you'll have to break things up.

 

[Ibero]

Thanks for your reply, so what would be the best way to do this? Is there a calculated field i can use for each threshold? Maybe with a calculation of some sort? Ultimately, i want to be able to open up the query and for it to show me the thresholds at the top, and the counts for how many people fall into each threshold... Is that possible? Thanks!

 

[GolfProRM]

My suggestion would be to build an unbound form that you can pull up. In that form, you can use textboxes that utilize the DCount function to pull the data.

If you're unsure how to use DCount, I'd recommend Access help for a start, and then you can search the forum for all sorts of Dcount examples to help you out.

 

[wazz]

a chart might also do the trick. haven't used it for some time.

 

[gemma-the-husky]

see if the PARTITION function will do this for you

this will split data into regular groups

 

[Dennisk]

I create a seperate query for each age band then place the queries in one final query for reporting.

 

[Ibero]

Thanks a lot for all your help. All of these methods are working, now i just need to speak to the powers that be to see which they prefer and deem as most efficient! Thanks.