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

Need help With access 2010 and VBA

P: 62
I have created db in access
In db tabel contains the age of employees
My job is to create column chart which shows how many employees have age between 20 to 25 or 25 to 30 and so on
I am not getting how i can group age of employees to generate column chart
Oct 24 '13 #1

✓ answered by ADezii

  1. Sounds like a candidate for the Partition() Function. I have set its Parameters to Group between Ages 0 to 100 at 5 Year Intervals.
  2. Sample Data in tblTest:
    Expand|Select|Wrap|Line Numbers
    1. Age
    2. 23
    3. 67
    4. 54
    5. 33
    6. 88
    7. 45
    8. 29
    9. 92
    10. 37
    11. 24
    12. 44
    13. 49
    14. 61
    15. 15
    16. 37
    17. 62
    18. 69
    19. 34
    20. 21
    21. 63
    22. 22
    23. 12
  3. SQL Statement:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCTROW Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
    2. FROM tblTest
    3. GROUP BY Partition([Age],0,100,5);
  4. Results:
    Expand|Select|Wrap|Line Numbers
    1. Age_Range   Range_Count
    2.  10: 14          1
    3.  15: 19          1
    4.  20: 24          4
    5.  25: 29          1
    6.  30: 34          2
    7.  35: 39          2
    8.  40: 44          1
    9.  45: 49          2
    10.  50: 54          1
    11.  60: 64          3
    12.  65: 69          2
    13.  85: 89          1
    14.  90: 94          1
    15.  
  5. Now, modify the Parameters and base your Chart on this Data.
P.S. - The other approach would be the inclusion of a 'Band Table' to perform Aggregate Functions on a Data Distribution, namely data residing within specific Ranges that are specified in a Band Table.

Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,221
Make a query that groups the employees by age and counts the keys or some field that is unique to each row. Something like
Expand|Select|Wrap|Line Numbers
  1. Select Age, Count(EmpID) from Employees Group By Age
Jim
Oct 24 '13 #2

Seth Schrock
Expert 2.5K+
P: 2,931
The problem with that Jim is that your method only groups the ages that are the same instead of grouping all the people with ages 20 to 25 in the same group which I believe is what the OP is wanting.

There might be another way to do this, but the method that comes to my mind is to use the Switch() function to assign each employee to a group. For example
Expand|Select|Wrap|Line Numbers
  1. SELECT EmpID
  2. , Age
  3. , Switch(Age Between 20 And 24, 1
  4.        , Age Between 25 And 29, 2
  5.        , Age Between 30 And 34, 3
  6.        , Age Between 35 And 39, 4
  7.        , Age Between 40 And 44, 5
  8.        , Age Between 45 And 49, 6) As AgeGroup
  9. FROM Employees
You would then do your bar chart based on the AgeGroup field.
Oct 24 '13 #3

ADezii
Expert 5K+
P: 8,597
  1. Sounds like a candidate for the Partition() Function. I have set its Parameters to Group between Ages 0 to 100 at 5 Year Intervals.
  2. Sample Data in tblTest:
    Expand|Select|Wrap|Line Numbers
    1. Age
    2. 23
    3. 67
    4. 54
    5. 33
    6. 88
    7. 45
    8. 29
    9. 92
    10. 37
    11. 24
    12. 44
    13. 49
    14. 61
    15. 15
    16. 37
    17. 62
    18. 69
    19. 34
    20. 21
    21. 63
    22. 22
    23. 12
  3. SQL Statement:
    Expand|Select|Wrap|Line Numbers
    1. SELECT DISTINCTROW Partition([Age],0,100,5) AS Age_Range, Count([Age]) AS Range_Count
    2. FROM tblTest
    3. GROUP BY Partition([Age],0,100,5);
  4. Results:
    Expand|Select|Wrap|Line Numbers
    1. Age_Range   Range_Count
    2.  10: 14          1
    3.  15: 19          1
    4.  20: 24          4
    5.  25: 29          1
    6.  30: 34          2
    7.  35: 39          2
    8.  40: 44          1
    9.  45: 49          2
    10.  50: 54          1
    11.  60: 64          3
    12.  65: 69          2
    13.  85: 89          1
    14.  90: 94          1
    15.  
  5. Now, modify the Parameters and base your Chart on this Data.
P.S. - The other approach would be the inclusion of a 'Band Table' to perform Aggregate Functions on a Data Distribution, namely data residing within specific Ranges that are specified in a Band Table.
Oct 24 '13 #4

P: 62
thaks every one i will try all the suggetions
Oct 24 '13 #5

Post your reply

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