473,324 Members | 1,646 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Need help With access 2010 and VBA

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.

4 1199
jimatqsi
1,271 Expert 1GB
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
2,965 Expert 2GB
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
8,834 Expert 8TB
  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
thaks every one i will try all the suggetions
Oct 24 '13 #5

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

Similar topics

5
by: jaad | last post by:
Hello, I have a database that was written in access 2007 on my pc. I wanted to work off site with the database so I uploaded it onto my laptop which is loaded with access 2010 beta. When I...
1
by: Alan Yim | last post by:
Hi folks, My company recently upgraded our Office suite from 2003 to 2010. The problem in particular is with an Access database that was originally designed in Access 2003. The code in question...
0
by: araman | last post by:
Hello, I have a form and subform . The Main form contains four boxes to enter criteria for the query that fills the subform. Outside of the navigation form it works great. when i add it to the...
2
by: Bill Boord | last post by:
I need to be able to shut off the AutoCorrect "feature" within Access 2010 code. I have utilized Application.SetOption with method strings for other startup requirements, but I cannot seem to find a...
5
by: neelsfer | last post by:
I got a new work laptop last week with Access 2010 pre-loaded.I also installed the new Office 2010 service pack 1. I then loaded 2 of my regularly used Access 2007 + service pack 2 applications on...
4
by: jbrumbau | last post by:
Hello, In Access 2010/2007, how do you prevent users from flipping into design view from the ribbon? I do not want to hide the entire ribbon (DoCmd.ShowToolbar "Ribbon", acToolbarNo) or even the...
4
beacon
by: beacon | last post by:
Hi everybody, My title may not exactly describe my issue, so please forgive my lack of creativity today. I've got a database that was created in Access 2003 that I've opened in Access 2010. I...
3
by: kais | last post by:
I am creating a database and have a combobox dropdown with two choices: "Open" or "Closed". When "closed" is chosen, I would like the "Reason for Closure" field to appear. This is what I have so...
1
by: rahul2310 | last post by:
I have created form where find record button is their find records works fine all i want is when record is found and data for that record is loaded in assigned textbox The code written behind...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.