range wise Count

Hello!

I have some data of people with "date of birth" I calculated the age of each person in a query. Now I want to Count the record in different ranges! i.e.

Less than 1 Year: .................
Between 1 to 5 Year: ................
Between 6 to 20 Year:...............
Between 21 to 40 Year:.............
Between 41 to 60 Year:.............
Between 61 to 80 Year:.............
Above 80:.....................

I counted the record for 1st range and it works but when I tried for 2nd both creteria doesn't work!

anyone can help...............

THANK YOU in ADVANCE:)

How do you calculate the age and count the first range in your queries?

What I did I made a query from main table where I have the birth dates by putting this statement
"SELECT main_tbl.file_no, main_tbl.dob, DateDiff("yyyy",[dob],Now()) AS Age
FROM main_tbl;"
on basis of above query I made another query to count the range wise record..... but only can count for one cretaria here is the statement:

SELECT Count([main_tbl Query1].Age) AS CountOfAge
FROM [main_tbl Query1]
WHERE ((([main_tbl Query1].Age) Between 1 And 5));

Here it counts between 1-5 ............
When in another column of query I put another creteria i.e
"=> and =<"

both creterias don't work


Sorry if I messed up the things............

Thank you for your support

Have a look at the attached sample database.
Table tbl_members is a table with one field for dates of birth
Query qry_age is a query to calculate the age range where 1 is for ages between 0 and 1, 2 is for ages 2 to 5 ... finally 7 is for over 80. I use a module compute_age_range to determine the age then assign the correct age range to the record.

Finally, I use a union query to produce the results.

This method will not display a count of 0 when there are no records for the age range. Suppose there are no records for persons born before 1928 i.e. no one was born before 1928, then the Above 80 range would not be display in this query.

Thank you Allan, the great Idea you gave, thank you very much it resolved my problem................

Thank you again

Hi Allan

Have downloaded your sample and find it ideal for my purposes.

However, I am not too familar with Union Select queries and have a further question:

What I need to understand is how to use the supplied solution embedded into my report. I need to display the ranges as subtotals of products and also accumulate a Grand Total.

e.g.

Product 1

Age range counts

Product 2

Age range counts

Grand Total

Age range counts

Thanks

Tony Randell

An UNION query combines the results of two or more independent queries or tables. SQL must be used to join the queries or tables.

In the attached example I have created seven queries, one for each age range as per the previous example database. These queries are not required after the union query is created. To join queries you must use the word UNION as shown in bold below

SELECT qry_age.product_ref, [Expr1] & " 0 - 1" AS Expr2, Count(qry_age.age_range) AS Total
FROM qry_age
WHERE (((qry_age.age_range)=1))
GROUP BY qry_age.product_ref,[Expr1] & " 0 - 1";

union SELECT qry_age.product_ref, [Expr1] & " 2 - 5" AS Expr2, Count(qry_age.age_range) AS Total
FROM qry_age
WHERE (((qry_age.age_range)=2))
GROUP BY qry_age.product_ref,[Expr1] & " 2 - 5";

I have also included a sample report that may suit your needs. I have added a table named tbl_products. The query qry_rpt_ages is used to link the union query and the products table to give you the record source for the report rpt_ages.

This report gives a sub total for each product and finally a grand total.

Hi Allan

Thanks so much for your examples - now I can get on with the job of incorporating them into my own DB.

Best Wishes

Tony Randell

Hi Allan

Just a note to say that there are a couple of errors in the second DB with products:

o Age range query for range 21 - 40 is Specified as 21 - 20
o Product No 5 has been omitted from the Product table

Apart from that - all is great.

Best Wishes

Tony Randell

Tony

Thank you for pointing out the errors, it was a copy and paste error. An updated version is attached.

Hi Allan

I have managed to modify the code so that I can compute each products age ranges as a % of the total for the product and as a % of the total for all products.

Now I want to make a new module that will do somethiing similar by grouping no of employes per client in the same way:

e.g 0 - 10 employees, 11 - 30 employees etc etc.

My Visual Basic skills are zero - how do I simply copy the existing module and rename it and change it to input no employees? I was hoping this would be a simple copy paste but...............?

Regards

Tony Randell

Tony
What determines your employees range, and the maximum range? In my example it was based on the date of birth.

I assume yours would be based on the number of employees in a department or section etc. as an example Department 1 has 5 employees Department 2 has 20 employees Department 3 has 8 employees etc.

Hi Allan

All I really want to know is how to create a new module. Then I want to copy the code created by you and modify it to handle the new logic.

In other words, how do I copy the existing logic and create a new function to take care of this logic? I dont want to replace the age related code but create a new module based on it. I am sure I can do it if I can just understand how to create the new function.

Hope that makes sense?

Regards

Tony Randell

Hi Allan

Just read my reply and dont think I explained myself very clearly.

I am doing a series of reports analysing Customers related to products. So the age-range relates to the ages of contacts who purchase the products and will be used in a report. Now I want to create a another report that analyzes the size of the Customer companies in terms of the no of employees and also related to products.

Your logic used in the age range lends itself very well to this kind of analysis. I just need to know how to replicate the code so that I can tailor it to meet the needs of each analysis. (As I said my VB knowledge is at a novice level)

Hope that helps to understand my query?

Regards

Tony Randell

Tony

The attached database is an example of the number of employees based on my previous age range. Have a look at the coding etc. Query qry_emp_range_summary will give you the summary.

Let me know if your need further explanation of the process.

Regards

Hi Allan

Thanks for the your new sample DB.

After much Googling, I had finally figured out how to create a new module and rename it in VBA! Today I plan to create new queries and your new example DB confirms that I was on the right track.

Thanks so much for all your help.

Tony Randell

Hi Allan

Just to let you know that everything is now working correctly.

Thanks for your help

Regards

Tony Randell

Quote:

---

Originally Posted by makpk

Hello!

I have some data of people with "date of birth" I calculated the age of each person in a query. Now I want to Count the record in different ranges! i.e.

Less than 1 Year: .................
Between 1 to 5 Year: ................
Between 6 to 20 Year:...............
Between 21 to 40 Year:.............
Between 41 to 60 Year:.............
Between 61 to 80 Year:.............
Above 80:.....................

I counted the record for 1st range and it works but when I tried for 2nd both creteria doesn't work!

anyone can help...............

THANK YOU in ADVANCE:)

---

I would add two fields to your table

Field = Category
Field = Category Description.

Write queries to categorize each record with a category number assigned to each of the birth date ranges. Cat1 to Cat7

Then write a final select query to group on category sorted ascending and count each category while also displaying the category description.