Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Convert for Percentage

    Hi, I am trying to create a PIE Chart in Access 2007 where data is stored in SQL Server 2005. I have the following query below which a member here kindly helped me with below which outputs the correct percentage figure in the percent_completed column. The output = 23.595505617977

    When i try and build a PIE Chart in Access, Access is showing me the figure as 2359.55%

    I have tried all combinations of Cast, Floor, Convert, Round to get the figure to 24% but to no avail e.g. SELECT CONVERT(int, 100.0 * d.completed / d.employees) but i can't get Access to show just 24% when selecting percentage for the figure in Access. However, it does just show 24 if i leave the value unformatted but it would be just nice to see the percentage sign after the figure on the chart. Is this possible?



    SELECT d.completed * 100.0 / d.employees AS percent_completed, dbo.CCompleted.Courses, dbo.Unit.Location
    FROM (SELECT COUNT(*) AS employees, e.Unit, COUNT(cc.EmployeeID) AS completed
    FROM dbo.Employees AS e LEFT OUTER JOIN
    dbo.CCompleted AS cc ON cc.EmployeeID = e.EmployeeID AND cc.EHS_Courseid = 1
    GROUP BY e.Unit
    HAVING (e.Unit = N'1')) AS d CROSS JOIN
    dbo.Courses CROSS JOIN
    dbo.Unit
    WHERE (dbo.Unit.UnitId = 1) AND (dbo.Courses.EHS_Courseid = 1)

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd try:
    Code:
    SELECT d.completed * 1e0 / d.employees AS percent_completed, dbo.CCompleted.Courses, dbo.Unit.Location
    FROM (SELECT COUNT(*) AS employees, e.Unit, COUNT(cc.EmployeeID) AS completed
    FROM dbo.Employees AS e LEFT OUTER JOIN
    dbo.CCompleted AS cc ON cc.EmployeeID = e.EmployeeID AND cc.EHS_Courseid = 1
    GROUP BY e.Unit
    HAVING (e.Unit = N'1')) AS d CROSS JOIN
    dbo.Courses CROSS JOIN
    dbo.Unit
    WHERE (dbo.Unit.UnitId = 1) AND (dbo.Courses.EHS_Courseid = 1)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2009
    Posts
    93
    Pat, that's excellent. As i'm a noob, i don't know what kind of black magic 1e0 means but will look up same.

    I've managed to throw in the Round option now also and am getting 24.00%. Is there anyway i can cut it down further to just get 24%

    SELECT ROUND(d.completed * 1e0 / d.employees, 2, 0) AS percent_completed

    If it's not possible, i will stick with the 23.65% figure as that is good enough for me .

    Many thanks

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The 1e0 is a "special purpose" wrinkle we sometimes use to "smoke out" kids wanting us do to their assignments for them. Based on your observation, it should only take you a minute or so to work it out, although there is a subtle reason that actually makes it useful in this case that I'd be happy to discuss privately.

    The formatting of the displayed value is actually handled by MS-Access, and can be set there in several different ways. Consult the MS-Access forum for more help there, but the Q&D solution is to right-click the pie graph and change the number formatting.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2009
    Posts
    93
    I like it

    I have already checked all the access format types and have percentage turned on which shows correctly 23.65%. I am just been finicky wanting to show 24% but the round function isn't doing what thought it would. I will continue on my learning quest

    I'd say you enjoy helping us kids out as you wouldn't be here otherwise
    Last edited by sullyman; 11-02-09 at 16:05.

  6. #6
    Join Date
    Oct 2009
    Posts
    93
    Pat, Got this version working also but no joy with Access


    SELECT ROUND(CAST(d.completed * 1 AS numeric) / CAST(d.employees AS numeric), 2, 0)

Posting Permissions

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