446,392 Members | 1,516 Online
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
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

 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   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 Sep 12 '07 #3

 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 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: Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers 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   Sep 12 '07 #4

 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   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! Sep 12 '07 #5

 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

 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

 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

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