Results 1 to 13 of 13
  1. #1
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16

    Pie chart for text boxes checked out of total needing to be checked

    I am trying to add a pie chart to a report that will display the total check boxes checked in a table. I have 110 things I need to examine, and I need to know what percent have been checked by category of "Satisfied", Not Satisfied" and "Not checked yet". I have a query that does the following:

    Field 1 - Counts the total number of records, which never changes
    Field 2 - Counts the number of times in the record set that the "Satisfied" box was checked
    Field 3 - Counts the number of times in the record set that the "Not Satisfied" box was checked

    I need a pie chart that uses Field 1 as the total (100%) for the pie chart and Field 2 and 3 are separate pie slices of the total that have been checked. No idea how to build that chart...


    Thanks!

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Add two more fields to your query which calculate Field2/Field1 and Field3/Field1 and in each case format as Percent.
    Use those field values for your chart
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Wow. I swear sometimes the obvious thing is the hardest to see...

  4. #4
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Here's my query:

    SELECT Count(Tbl_Requirements.Requirement_Number) AS CountOfRequirement_Number, Sum(Abs([Requirement_Satisfied])) AS Expr1, Sum(Abs([Requirement_Other_Than_Satisfied])) AS Expr2
    FROM Tbl_Requirements;

    The query returns valid counts as-is, but when I try to make another column for the math expression to divide column 2 by column 1 to get a percent, it prompts me for a value for Expr1.

  5. #5
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by Bill the Cat View Post
    Here's my query:

    SELECT Count(Tbl_Requirements.Requirement_Number) AS CountOfRequirement_Number, Sum(Abs([Requirement_Satisfied])) AS Expr1, Sum(Abs([Requirement_Other_Than_Satisfied])) AS Expr2
    FROM Tbl_Requirements;

    The query returns valid counts as-is, but when I try to make another column for the math expression to divide column 2 by column 1 to get a percent, it prompts me for a value for Expr1.
    Never mind. I forgot to change the Total to Expression...

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    That's not really the issue.
    Sorry. I should have made it clear that you either need to create a second query based on the first or repeat the calculations as below

    Code:
    SELECT Count(Tbl_Requirements.Requirement_Number) AS CountOfRequirement_Number, 
    Sum(Abs([Requirement_Satisfied])) AS TotalSatisfied, 
    Sum(Abs([Requirement_Other_Than_Satisfied])) AS TotalOther, 
    Sum(Abs([Requirement_Satisfied])) / Count(Tbl_Requirements.Requirement_Number) AS PercentSatisfied,
    Sum(Abs([Requirement_Other_Than_Satisfied])) / Count(Tbl_Requirements.Requirement_Number) AS PercentOther
    FROM Tbl_Requirements;
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by isladogs View Post
    That's not really the issue.
    Sorry. I should have made it clear that you either need to create a second query based on the first or repeat the calculations as below

    Thanks for the correction. I now have the correct results, but when I try to add the pie chart to the report, it asks for Data and Series. I have tried every combination to try to get them to display the correct pie slices to no avail. What field goes in the "series" block and which goes in the "data" block?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I've only just noticed this reply as I've had major computer issues all day with a corrupted hard drive

    Until now I've just been focusing on how you can get the % data in a query but that doesn't help for your chart

    In order to display your data in a pie chart, you need to show your data in two columns as in the example below:

    Click image for larger version. 

Name:	PieChart.PNG 
Views:	22 
Size:	25.9 KB 
ID:	40544

    So in your case, you need to have a query with two columns
    a) Requirement - with values Satisfied / OtherThanSatisfied
    b) RequirementNumber or RequirementPercent - with the number or % values

    If you can create that query, then use that for the pie chart.
    Sorry if I've caused any confusion whilst being distracted with my own issues
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by isladogs View Post

    If you can create that query, then use that for the pie chart.
    Sorry if I've caused any confusion whilst being distracted with my own issues
    I appreciate the time you are taking. I have no clue how to make that query. I know redesigning the table and eliminate the checkboxes is the most prudent route, but honestly, I've done too much work on the other parts of the database to redesign tables, queries and forms. Thanks again

    Bill

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If your table is like a spreadsheet then each task in Access will be much harder than it should be.
    If that is the case then a redesign would be advisable.

    However, for now a union query should provide what you need for the pie chart.
    Code:
    SELECT 'Satisfied' AS RequirementType, Sum(Requirement_Satisfied) AS TotalNumber
    FROM Tbl_Requirements
    UNION  
    SELECT 'Other Than Satisfied' AS RequirementType, Sum(Requirement_Other_Than_Satisfied) AS TotalNumber
    FROM Tbl_Requirements;
    
    You don't need % values or overall totals in the above as the pie chart will automatically calculate those.

    If that doesn't work, can you upload the relevant parts of your database and I'll see if I can create a suitable query for your chart
    Omit anything not related to the task and anonymise any sensitive data.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by isladogs View Post
    If your table is like a spreadsheet then each task in Access will be much harder than it should be.
    If that is the case then a redesign would be advisable.

    However, for now a union query should provide what you need for the pie chart.
    Code:
    SELECT 'Satisfied' AS RequirementType, Sum(Requirement_Satisfied) AS TotalNumber
    FROM Tbl_Requirements
    UNION  
    SELECT 'Other Than Satisfied' AS RequirementType, Sum(Requirement_Other_Than_Satisfied) AS TotalNumber
    FROM Tbl_Requirements;
    
    You don't need % values or overall totals in the above as the pie chart will automatically calculate those.

    If that doesn't work, can you upload the relevant parts of your database and I'll see if I can create a suitable query for your chart
    Omit anything not related to the task and anonymise any sensitive data.
    It worked well enough for what I need. Cheers!

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome. Sorry I took a detour on the way to the solution...
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    Bill the Cat is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Virginia
    Posts
    16
    Quote Originally Posted by isladogs View Post
    You're welcome. Sorry I took a detour on the way to the solution...
    No sweat. I actually ended up using that for another requirement I had.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 03-17-2016, 10:42 AM
  2. Count Checked boxes on report
    By BLFOSTER in forum Reports
    Replies: 2
    Last Post: 06-22-2015, 09:33 AM
  3. Total of checked field
    By AccessThomas in forum Access
    Replies: 3
    Last Post: 05-08-2015, 12:03 PM
  4. Replies: 2
    Last Post: 03-08-2014, 02:45 AM
  5. Checked Box + Date = Checked box
    By ItsATJ in forum Access
    Replies: 12
    Last Post: 09-03-2013, 10:25 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums