By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,516 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

Report by Age groups

P: 2
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
Sep 11 '07 #1
Share this Question
Share on Google+
10 Replies


Expert 100+
P: 296
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.
Sep 11 '07 #2

Jim Doherty
Expert 100+
P: 897
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. TRANSFORM Count(tblTestResults.Age) AS CountOfAge
  3. SELECT tblTestResults.testrslt
  4. FROM tblTestResults
  5. GROUP BY tblTestResults.testrslt
  6. PIVOT Partition(Int([Age]),0,100,5);
  7.  
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
Sep 12 '07 #3

ADezii
Expert 5K+
P: 8,669
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
  2. FROM tblTest
  3. GROUP BY [Testrslt], Partition([Age],0,100,5);
Try:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW [Testrslt], Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
  2. FROM tblTest
  3. GROUP BY [Testrslt], Partition([Age],0,100,5);
OUTPUT From Sample Data:
Expand|Select|Wrap|Line Numbers
  1. Testrslt              Age_Range              Range_Count
  2. Satisfactory          15: 19                   2
  3. Satisfactory          20: 24                   2
  4. Satisfactory          50: 54                   1
  5. Satisfactory          60: 64                  12
  6. Satisfactory          75: 79                   8
  7. Unsatisfactory          15: 19                     1
  8. Unsatisfactory          20: 24                     1
  9. Unsatisfactory          45: 49                     5
  10. Unsatisfactory          55: 59                    21
  11. Unsatisfactory          60: 64                     9
  12. Unsatisfactory          80: 84                     1
  13.  
Sep 12 '07 #4

ADezii
Expert 5K+
P: 8,669
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. TRANSFORM Count(tblTestResults.Age) AS CountOfAge
  3. SELECT tblTestResults.testrslt
  4. FROM tblTestResults
  5. GROUP BY tblTestResults.testrslt
  6. PIVOT Partition(Int([Age]),0,100,5);
  7.  
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!
Sep 12 '07 #5

Jim Doherty
Expert 100+
P: 897
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 :))
Sep 12 '07 #6

Expert 100+
P: 296
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!
Sep 12 '07 #7

ADezii
Expert 5K+
P: 8,669
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!
Sep 12 '07 #8

Expert 100+
P: 296
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...
Sep 12 '07 #9

ADezii
Expert 5K+
P: 8,669
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.
Sep 12 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
You can see the full list of all articles here.
Sep 13 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.