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!
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 =<"
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.
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.
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.
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...............?
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.
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.
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)
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.
range wise Count
Reply With Quote