Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Bar Chart (2003)

    I have a query which calculates the number of rejects, and their categories. I want to create a bar chart which show what percentage of the total rejects, there is for each categorie. I have the chart showing me the quantities, it's the percentages I don't know how to do

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bar Chart (2003)

    You can create a new query based on the one you have that calculates the percentage per category: divide the number per category by the total number of rejects. You can compute the total number of rejects in a separate totals query that returns a single record, and include that in the new query, or you can use a DCount expression.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Bar Chart (2003)

    I nearly have it. I would like to close the columns up (see attached), and get the Y axis to read 100 as this is the total percentage

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bar Chart (2003)

    Open the form (or report) in design view.
    The chart control may display a sample graph, this doesn't matter.
    Double click the chart to edit it.

    Click on a column to select the data series.
    Select Format | Selected Data Series...
    Activate the Options tab.
    Set Gap Width to 0.
    Click OK.

    Click on the value axis (y axis) to select it.
    Select Format | Selected Axis...
    Activate the Scale tab.
    Set Maximum to 100. This will automatically clear the associated "Auto" check box.
    Click OK.

    Close and save the form

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Bar Chart (2003)

    Just one glitch left. In my expression in the query I've got No of Category/Total * 100. This gives me the correct figure in the graph, which is in one case, 56.25. I want to show the % sign. If I change the format on the graph, I obviously get 5625%. If I remove the *100 from the expression, the labels in the graph are right, but the bars drop down to the bottom. Is there a way of getting the % to show without messing the whole graph up. Apart from this minor detail, everythings fine.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bar Chart (2003)

    Drop the *100, format as a percentage, and set the Maximum value of the axis to 1 (explanation: 100% = 100/100 = 1).

  7. #7
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Bar Chart (2003)

    Everythings seems to be working, except when I ran a graph for a certain period, I get a total of 101%. Is this because of the way the figures are rounded up in the query?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bar Chart (2003)

    Yes, that's most probably a rounding error.

  9. #9
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Bar Chart (2003)

    I have a field that creates the percentage, which is Percentage[QtyComps]/[Total]). I then created a field TotalPercentage:Round([Percentage]). When run, I'm asked for 'Percentage'.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bar Chart (2003)

    Have you placed TotalPercentage to the right of the Percentage column?
    Is this in a Totals query? If so, the calculation may be too complex. You could create a new query based on the query that defines Percentage, and define TotalPercentage in the new query.

  11. #11
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Bar Chart (2003)

    I may be making this too complex. I have a query which collects the data, a query for the totals for each record, a query which puts these together to get the totals for each month, and a query which creates the percentage. Now I need one which will round the percentages up. Does this seem right?

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Bar Chart (2003)

    Not knowing what your data look like, I cannot really tell. Why do you need a query to get totals for each record?
    You could calculate the percentage and round it in one go:

    Percentage: Round([QtyComps]/[Total],2)

    or something similar.

  13. #13
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Bar Chart (2003)

    In the table I have Categories such as Defects, Paperwork, Deliveries. So for each month, I am calculating the total number of categories, plus how many are of each type. This is how I work out the percentages. I'll try stripping the queries down.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •