age and age ranges

[reportyemi]

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 DEWEY]

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);

 

[John W. Vinson]

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].

 

[reportyemi]

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);

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].

 

[reportyemi]

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

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);

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?

 

[reportyemi]

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, 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);

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 Spencer]

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
'====================================================

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, 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);

On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi

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].

 

[reportyemi]

it wORKED - YOUR SECOND FORMULA. Thank you very much

Yemi

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
'====================================================

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, 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);

:

On Fri, 17 Apr 2009 21:57:01 -0700, reportyemi

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].