Report by Age groups

[will04]

Hello everyone,

I have a table which includes 2 fields, patients age [age] and results of a screening test [testrslt].

The age groups are less than 20, 20 - 24, 25 - 29, 30 - 34, ...65 - 69, 70+
The test result can either be Satisfactory or Unsatisfactory

Is it possible to generate a report on the number of occurrences of each test result type by age group.



Thanks


Will

[mlcampeau]

Hello everyone,

I have a table which includes 2 fields, patients age [age] and results of a screening test [testrslt].

The age groups are less than 20, 20 - 24, 25 - 29, 30 - 34, ...65 - 69, 70+
The test result can either be Satisfactory or Unsatisfactory

Is it possible to generate a report on the number of occurrences of each test result type by age group.

Thanks

Will

You can try something like this:
Create textboxes for each age group and input the following expressions and label each textbox accordingly:
=Sum(IIF([Age] < 20 AND [testrslt]="Satisfactory",1,0))
=Sum(IIF([Age] < 20 AND [testrslt]="Unsatisfactory",1,0))
=Sum(IIf(([Age] Between 20 And 24) AND [testrslt]="Satisfactory",1,0))
etc......
I hope you get the picture with that.

[Jim Doherty]

Hello everyone,

I have a table which includes 2 fields, patients age [age] and results of a screening test [testrslt].

The age groups are less than 20, 20 - 24, 25 - 29, 30 - 34, ...65 - 69, 70+
The test result can either be Satisfactory or Unsatisfactory

Is it possible to generate a report on the number of occurrences of each test result type by age group.



Thanks


Will

You might wish to look at the PARTITION function in Access which is specifically intended grouping such as this. The example you can find in help and practice on it using the Northwind database

Search the word PARTITION in Access help

As an idea how it applies in your circumstances imagine you have a table called tblTestResults with two fields one called Age and another called tstrslt .....then the SQL syntax that you can use and paste into the query window 'from here' to support the bandings you speak of is as follows

 
TRANSFORM Count(tblTestResults.Age) AS CountOfAge
SELECT tblTestResults.testrslt
FROM tblTestResults
GROUP BY tblTestResults.testrslt
PIVOT Partition(Int([Age]),0,100,5);
 

The help on this in Access is fully explanatory

According to your explanation of your data the above SQL statement will display when run a resultant dataset giving you a matrix of two rows representing 'Satisfactory' and 'Unsatisfactory' and cross tabulated number of columns between zero years of age and 100 years of age with each column representing an age band of ie: 20:24, 25:29,30:34,35:39.....and so on. It will only create these columns of course where there exists data in your table to support the output

If you study it you will see the Partition(Int([Age]),0,100,5) is the effective element with the arguments 0 representing the 'start' and 100 representing the 'stop' point and the figure 5 representing the incremental value Access should use to create the output columns

You could always then output the query to excel and do what you wish with the figures produced. This is probably a better option for you because creating an Access report based on the output 'might' (I,m not saying it will but it just might) cause you a problem due to the nature of the dynamic column creation giving you columns according to the data held. The potential problem being that if at some stage you removed from your database 30-34 year old people for instance the report would throw an error on opening because the report would still have a field for that banding

Hope this helps you

Regards

Jim

[ADezii]

Hello everyone,

I have a table which includes 2 fields, patients age [age] and results of a screening test [testrslt].

The age groups are less than 20, 20 - 24, 25 - 29, 30 - 34, ...65 - 69, 70+
The test result can either be Satisfactory or Unsatisfactory

Is it possible to generate a report on the number of occurrences of each test result type by age group.



Thanks


Will

Try:

SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
FROM tblTest
GROUP BY [Testrslt], Partition([Age],0,100,5);

Try:

SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
FROM tblTest
GROUP BY [Testrslt], Partition([Age],0,100,5);

OUTPUT From Sample Data:

Testrslt              Age_Range              Range_Count
Satisfactory          15: 19                   2
Satisfactory          20: 24                   2
Satisfactory          50: 54                   1
Satisfactory          60: 64                  12
Satisfactory          75: 79                   8
Unsatisfactory          15: 19                     1
Unsatisfactory          20: 24                     1
Unsatisfactory          45: 49                     5
Unsatisfactory          55: 59                    21
Unsatisfactory          60: 64                     9
Unsatisfactory          80: 84                     1
 

[ADezii]

You might wish to look at the PARTITION function in Access which is specifically intended grouping such as this. The example you can find in help and practice on it using the Northwind database

Search the word PARTITION in Access help

As an idea how it applies in your circumstances imagine you have a table called tblTestResults with two fields one called Age and another called tstrslt .....then the SQL syntax that you can use and paste into the query window 'from here' to support the bandings you speak of is as follows

 
TRANSFORM Count(tblTestResults.Age) AS CountOfAge
SELECT tblTestResults.testrslt
FROM tblTestResults
GROUP BY tblTestResults.testrslt
PIVOT Partition(Int([Age]),0,100,5);
 

The help on this in Access is fully explanatory

According to your explanation of your data the above SQL statement will display when run a resultant dataset giving you a matrix of two rows representing 'Satisfactory' and 'Unsatisfactory' and cross tabulated number of columns between zero years of age and 100 years of age with each column representing an age band of ie: 20:24, 25:29,30:34,35:39.....and so on. It will only create these columns of course where there exists data in your table to support the output

If you study it you will see the Partition(Int([Age]),0,100,5) is the effective element with the arguments 0 representing the 'start' and 100 representing the 'stop' point and the figure 5 representing the incremental value Access should use to create the output columns

You could always then output the query to excel and do what you wish with the figures produced. This is probably a better option for you because creating an Access report based on the output 'might' (I,m not saying it will but it just might) cause you a problem due to the nature of the dynamic column creation giving you columns according to the data held. The potential problem being that if at some stage you removed from your database 30-34 year old people for instance the report would throw an error on opening because the report would still have a field for that banding

Hope this helps you

Regards

Jim

Hey Jim, thought I was the only one in the world that knew about PARTITION! Nice find!

[Jim Doherty]

Hey Jim, thought I was the only one in the world that knew about PARTITION! Nice find!

Hahaha the feeling is somewhat like sitting by a lake with a fishing rod theres always a use for it someday

Jim :))

[mlcampeau]

I have never heard of the Partition function. I had to do something similar on one of my reports and I used the method I suggested up above. It does work, but this is good to know too!

[ADezii]

I have never heard of the Partition function. I had to do something similar on one of my reports and I used the method I suggested up above. It does work, but this is good to know too!

Being as it invoked some interest, I may actually incorporate it into the Tip of the Week!

[mlcampeau]

Being as it invoked some interest, I may actually incorporate it into the Tip of the Week!

Not to sound off topic or anything, but where do the old Tip of the Weeks go? When I go to the sticky it only shows this weeks...

[ADezii]

Not to sound off topic or anything, but where do the old Tip of the Weeks go? When I go to the sticky it only shows this weeks...

Articles ==> Access ==> you'll see all the Tips listed by Topic. Tips are usually retained for a week, then they get transferred to the Articles/Access area.

[MMcCarthy]

You can see the full list of all articles here.