If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
age and age ranges
I have a form that has the age of patients. But in the report i want to
report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? |
#2
|
|||
|
|||
age and age ranges
Use partition --
SELECT Partition([Age],10,999,4) AS [Age Group], Count(YourTable.[Age]) AS [CountOfAge] FROM YourTable GROUP BY Partition([Age],10,999,4); "reportyemi" wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? |
#3
|
|||
|
|||
age and age ranges
On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi
wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? Karl's Partition function is certainly one approach; another would be to have a "ranges" table. The simplest way (not properly normalized since the ranges aren't independent) would be a table with three fields: Low, High and Rangename (with values like 10, 14, "10-14", 15, 19, "15-19" and so on). You could also use 0, 2, "Infant", 3, 6, "Toddler", 7, 10, "Child", etc. Your report could be base on a query including this range table, with a criterion on Low of = [Age] and on High of = [Age]. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
age and age ranges
Karl, I tried this but cannot seem to get it to function. It says the syntax
error. This is what i did below and placed it in the criteria section. Is that where i should put it? Partition ( [tblMaternal]![Age] , 10, 999,4) AS Range, Count ( [tblMaternal]![Age] ) AS count FROM [tblMaternal]![Age] GROUP BY Partition ( [tblMaternal]![Age] , 10, 999, 4); "John W. Vinson" wrote: On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? Karl's Partition function is certainly one approach; another would be to have a "ranges" table. The simplest way (not properly normalized since the ranges aren't independent) would be a table with three fields: Low, High and Rangename (with values like 10, 14, "10-14", 15, 19, "15-19" and so on). You could also use 0, 2, "Infant", 3, 6, "Toddler", 7, 10, "Child", etc. Your report could be base on a query including this range table, with a criterion on Low of = [Age] and on High of = [Age]. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
age and age ranges
Karl, I was very pleased as i did the partition BUT when i looked at it , it looks at the age ID field which also has 1,2,3,4,5,6.....numbers and not the Age field. I have a tlkpage table which my form refernences . This table begining is like this ID Age 1 10 2 11 3 12 4 13 5 14 and so on. The reason the first age is 10 is that is the (unfortunately) the lowest age patient may get pregnant. When i do the partition formula, all the ranges come out as you advised but it reads the ID column as the ages and therofere infers the wrong age range. this is my sql SELECT Partition([tblMaternal.Age],10,55,5) AS [Age Group], Count([tblMaternal.Age]) AS theCount FROM tblMaternal GROUP BY Partition([tblMaternal]![Age],10,55,5); so with 3 women with ages 12, 17 and 25 for example the result it gives is :9 2 10 - 14 1 15 - 19 1 i cant get the formula to reference the second column. Please help me again yemi "KARL DEWEY" wrote: Use partition -- SELECT Partition([Age],10,999,4) AS [Age Group], Count(YourTable.[Age]) AS [CountOfAge] FROM YourTable GROUP BY Partition([Age],10,999,4); "reportyemi" wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? |
#6
|
|||
|
|||
age and age ranges
Karl, I was very pleased as i did the partition BUT when i looked at it , it
looks at the age ID field which also has 1,2,3,4,5,6.....numbers and not the Age field. I have a tlkpage table which my form refernences . This table begining is like this ID Age 1 10 2 11 3 12 4 13 5 14 and so on. The reason the first age is 10 is that is the (unfortunately) the lowest age patient may get pregnant. When i do the partition formula, all the ranges come out as you advised but it reads the ID column as the ages and therofere infers the wrong age range. this is my sql SELECT Partition([tblMaternal.Age],10,55,5) AS [Age Group], Count([tblMaternal.Age]) AS theCount FROM tblMaternal GROUP BY Partition([tblMaternal]![Age],10,55,5); so with 3 women with ages 12, 17 and 25 for example the result it gives is :9 2 10 - 14 1 15 - 19 1 i cant get the formula to reference the second column. Please help me again yemi "reportyemi" wrote: Karl, I tried this but cannot seem to get it to function. It says the syntax error. This is what i did below and placed it in the criteria section. Is that where i should put it? Partition ( [tblMaternal]![Age] , 10, 999,4) AS Range, Count ( [tblMaternal]![Age] ) AS count FROM [tblMaternal]![Age] GROUP BY Partition ( [tblMaternal]![Age] , 10, 999, 4); "John W. Vinson" wrote: On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? Karl's Partition function is certainly one approach; another would be to have a "ranges" table. The simplest way (not properly normalized since the ranges aren't independent) would be a table with three fields: Low, High and Rangename (with values like 10, 14, "10-14", 15, 19, "15-19" and so on). You could also use 0, 2, "Infant", 3, 6, "Toddler", 7, 10, "Child", etc. Your report could be base on a query including this range table, with a criterion on Low of = [Age] and on High of = [Age]. -- John W. Vinson [MVP] |
#7
|
|||
|
|||
age and age ranges
You need to add in your other table to the query
SELECT Partition([OtherTable].[Age],10,55,5) AS [Age Group], Count([tblMaternal.Age]) AS theCount FROM tblMaternal INNER JOIN [OtherTable] On tblMaternal.Age = [OtherTable].ID GROUP BY Partition([OtherTable].[Age],10,55,5); Of course, you can do close to the same thing with some math and not use the partition function or the additional table at all. (tblMaternal.Age\5)*5 & " - " & (1+tblMaternal.Age\5)*5-1 '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === reportyemi wrote: Karl, I was very pleased as i did the partition BUT when i looked at it , it looks at the age ID field which also has 1,2,3,4,5,6.....numbers and not the Age field. I have a tlkpage table which my form refernences . This table begining is like this ID Age 1 10 2 11 3 12 4 13 5 14 and so on. The reason the first age is 10 is that is the (unfortunately) the lowest age patient may get pregnant. When i do the partition formula, all the ranges come out as you advised but it reads the ID column as the ages and therofere infers the wrong age range. this is my sql SELECT Partition([tblMaternal.Age],10,55,5) AS [Age Group], Count([tblMaternal.Age]) AS theCount FROM tblMaternal GROUP BY Partition([tblMaternal]![Age],10,55,5); so with 3 women with ages 12, 17 and 25 for example the result it gives is :9 2 10 - 14 1 15 - 19 1 i cant get the formula to reference the second column. Please help me again yemi "reportyemi" wrote: Karl, I tried this but cannot seem to get it to function. It says the syntax error. This is what i did below and placed it in the criteria section. Is that where i should put it? Partition ( [tblMaternal]![Age] , 10, 999,4) AS Range, Count ( [tblMaternal]![Age] ) AS count FROM [tblMaternal]![Age] GROUP BY Partition ( [tblMaternal]![Age] , 10, 999, 4); "John W. Vinson" wrote: On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? Karl's Partition function is certainly one approach; another would be to have a "ranges" table. The simplest way (not properly normalized since the ranges aren't independent) would be a table with three fields: Low, High and Rangename (with values like 10, 14, "10-14", 15, 19, "15-19" and so on). You could also use 0, 2, "Infant", 3, 6, "Toddler", 7, 10, "Child", etc. Your report could be base on a query including this range table, with a criterion on Low of = [Age] and on High of = [Age]. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
age and age ranges
it wORKED - YOUR SECOND FORMULA. Thank you very much
Yemi "John Spencer" wrote: You need to add in your other table to the query SELECT Partition([OtherTable].[Age],10,55,5) AS [Age Group], Count([tblMaternal.Age]) AS theCount FROM tblMaternal INNER JOIN [OtherTable] On tblMaternal.Age = [OtherTable].ID GROUP BY Partition([OtherTable].[Age],10,55,5); Of course, you can do close to the same thing with some math and not use the partition function or the additional table at all. (tblMaternal.Age\5)*5 & " - " & (1+tblMaternal.Age\5)*5-1 '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === reportyemi wrote: Karl, I was very pleased as i did the partition BUT when i looked at it , it looks at the age ID field which also has 1,2,3,4,5,6.....numbers and not the Age field. I have a tlkpage table which my form refernences . This table begining is like this ID Age 1 10 2 11 3 12 4 13 5 14 and so on. The reason the first age is 10 is that is the (unfortunately) the lowest age patient may get pregnant. When i do the partition formula, all the ranges come out as you advised but it reads the ID column as the ages and therofere infers the wrong age range. this is my sql SELECT Partition([tblMaternal.Age],10,55,5) AS [Age Group], Count([tblMaternal.Age]) AS theCount FROM tblMaternal GROUP BY Partition([tblMaternal]![Age],10,55,5); so with 3 women with ages 12, 17 and 25 for example the result it gives is :9 2 10 - 14 1 15 - 19 1 i cant get the formula to reference the second column. Please help me again yemi "reportyemi" wrote: Karl, I tried this but cannot seem to get it to function. It says the syntax error. This is what i did below and placed it in the criteria section. Is that where i should put it? Partition ( [tblMaternal]![Age] , 10, 999,4) AS Range, Count ( [tblMaternal]![Age] ) AS count FROM [tblMaternal]![Age] GROUP BY Partition ( [tblMaternal]![Age] , 10, 999, 4); "John W. Vinson" wrote: On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi wrote: I have a form that has the age of patients. But in the report i want to report the age in age ranges 10-14, 15-19,20-24,25-29 and so on . I want to be able to count the mothers in the different age ranges and determine what age ranges present at the hospital. In otherwords, my report should read age range 10 - 14, 5 women; age range 15-19, 15 women; age range 20 -24, 12 women and so on. How can i do this? The only way i thought was to actually have checkboxes in my form with the age ranges and get the indidual to fill it based on age. Then, in the report, we simply count the numebr of checks But is there any other way? Karl's Partition function is certainly one approach; another would be to have a "ranges" table. The simplest way (not properly normalized since the ranges aren't independent) would be a table with three fields: Low, High and Rangename (with values like 10, 14, "10-14", 15, 19, "15-19" and so on). You could also use 0, 2, "Infant", 3, 6, "Toddler", 7, 10, "Child", etc. Your report could be base on a query including this range table, with a criterion on Low of = [Age] and on High of = [Age]. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|