Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2009
    Posts
    93

    Unanswered: Need help please

    I need to create a query to show the percentage of employees who have completed a course.

    I have 3 tables
    Employees (PK_Employeeid, Employee)
    Courses (PK_Courseid, Course)
    Courses_Completed (PK_Completedid, FK_Employeeid, FK_Courseid)

    For example, if there are 100 employees and only 25 have completed the course, i would like the Pie chart to show 25% etc.

    I am a bit lost on how to create such a query

  2. #2
    Join Date
    Oct 2009
    Posts
    93
    I am able to count the no of employees = 100 from the following query

    SELECT COUNT(Employee) AS EmployeeNo
    FROM dbo.Employees


    I am also able to count the no of employees that completed the course = 25 from the following query


    SELECT COUNT(EmployeeID) AS AttendedNo
    FROM dbo.Courses_Completed
    WHERE (Courseid = 2)
    GROUP BY EmployeeID


    How can i join these queries so i can divide 100/25 to get a percentage of employees who have attended the course

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do your Microsoft Access tables have a dbo prefix? maybe you should have posted in the SQL Server forum?

    Code:
    SELECT employees
         , completed
         , 100.0 * completed / employees AS percent_completed
      FROM (
           SELECT COUNT(*) AS employees
                , COUNT(cc.FK_Employeeid) AS completed
             FROM dbo.Employees AS e
           LEFT OUTER
             JOIN dbo.Courses_Completed AS cc
               ON cc.FK_Employeeid = e.PK_Employeeid
              AND cc.FK_Courseid = 2
           ) AS d
    also, please note, your Courses_Completed should ~not~ have its own identity column (PK_Completedid)

    instead, the table should look like this --
    Code:
    CREATE TABLE dbo.Courses_Completed
    ( FK_Employeeid INTEGER NOT NULL 
    , FK_Courseid  INTEGER NOT NULL 
    , PRIMARY KEY ( FK_Employeeid, FK_Courseid )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2009
    Posts
    93
    Hi R937

    Thanks. I am trying to develop a frontend to SQL Server in Access. Much appreciated. I will try to get it working now.
    Last edited by sullyman; 11-01-09 at 13:27.

  5. #5
    Join Date
    Oct 2009
    Posts
    93
    Hi R937,

    That is brilliant. The percentage in the cell is coming out like 1.024590163934. How do i round this number to 1% or 2% etc.

    Many thanks.

    Also, why would you not have a PK on the CoursesCompleted table. Would it be bad design to have one?

  6. #6
    Join Date
    Oct 2009
    Posts
    93
    Hi R937,

    Thanks a million. It works great. One strange thing is happening though. I've managed to group employees by unit and when i group it by Unit1, 90 employees are totaled when there should be only 89. But for all other groups, the number is right for the employee total. Am i doing something wrong in the code below?


    SELECT employees, completed, 100.0 * completed / employees AS percent_completed
    FROM (SELECT COUNT(*) AS employees, Unit, COUNT(cc.Employeeid) AS completed
    FROM dbo.Employees AS e LEFT OUTER JOIN
    CCompleted AS cc ON cc.Employeeid = e.Employeeid AND cc.Courseid = 2
    GROUP BY e.Unit
    HAVING (e.Unit = N'1')) d

  7. #7
    Join Date
    Oct 2009
    Posts
    93
    I have tried the following but the overall employees number is out by one (should be 120 instead of 121 which is returned. Have checked the table and 120 are listed. Any ideas?

    SELECT employees, completed, 100.0 * completed / employees AS percent_completed
    FROM (SELECT COUNT(*) AS employees, COUNT(cc.EmployeeID) AS completed
    FROM dbo.Employees AS e LEFT OUTER JOIN
    dbo.LK_Safety_History AS cc ON cc.EmployeeID = e.EmployeeID AND cc.EHS_Courseid = 2) AS d

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sullyman View Post
    Any ideas?
    yes

    there are 121 employees

    check again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2009
    Posts
    93
    HI R937,

    I have checked and something is amiss.

    It is adding the the number of employees records in the ccompleted table as new employees to the employees table when i run the query.

    Is is something to do with the type of join?

    SELECT employees, completed, 100.0 * completed / employees AS percent_completed
    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
    GROUP BY e.Unit
    HAVING (e.Unit = N'1')) AS d

  10. #10
    Join Date
    Oct 2009
    Posts
    93
    Hi R937

    I see what is happening now. If i select an employee with more than one course, it starts adding to the total employee number.

    The employee will be able to do more than one course so i think this is where the problem lies. Any ideas?

  11. #11
    Join Date
    Oct 2009
    Posts
    93
    Can you please take a look as it's driving me bonkers all day

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sullyman View Post
    Can you please take a look as it's driving me bonkers all day
    whoa

    i decide to have an afternoon nap and you go on a posting rampage

    what's all this about a unit? you never mentioned a unit

    and what's the deal with N? why do you have unit = N'1'?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Oct 2009
    Posts
    93
    Sorry, i am tearing my hair out with this. Employees are from different offices (units). don't know about the N thing. Access wizard place this in

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so you want percentage by unit for all units? or just the percentage for a given unit? because that's what it looks like, you on;y want the percentage for unit 1

    c'mon, man, be more specific in your problem statement

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  15. #15
    Join Date
    Oct 2009
    Posts
    93
    Sorry again. I am after spotting the problem. I had duplicate test records in the ccompleted table. Really sorry. Thanks for your help today with this. Without you, i would not have got where i am now. Many thanks

Posting Permissions

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