Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3
  1. #1
    Senior Member toadeny's Avatar
    Join Date
    May 2003
    Location
    Wellington, New Zealand
    Posts
    188
    Member #
    1426
    Hi there

    I am looking for a way in MS Access SQL to bring up the top 5 employees who have never failed a test, and if they have failed, they will not be shown on this list.

    and also

    I wish to see all of the employees that have failed at least one test and also passed one test at the same time.

    Does any one have any ideas on any way to sovle these problems?

    I would be extremely grateful if anyone would be able to solve any one if not all of these problems

    These are the tables and their Fields
    HISTORY
    TestID
    TestedON
    EmployeeId
    TestType
    Passed (boolean)
    DateStarted

    TEST
    TestType
    TestDesc
    Critical
    Unit

    EMPLOYEE
    EmployeeId
    FirstName
    Surname
    Date Of Birth
    DateStarted

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Part of it depends on how you want to measure the top 5 employees. You may have 20 employees that have never failed. I'm assuming you want to list the top 5 with the largest amount of tests taken, or perhaps by DateStarted?

    Code:
    SELECT E.EmployeeId, E.FirstName, E.Surname, H.TestID
    FROM HISTORY as H, EMPLOYEE as E
    WHERE H.Passed = 1
    AND H.EmployeeID = E.EmployeeID
    ORDER BY E.EmployeeId, H.TestID ASC
    This code should find all the tests that have been passed. I think what you want will require more than one query. The only way I can see doing it with only 1 query is to add a value to the Employee table (a boolean) that is TRUE if they have never taken a test or have taken at least one test and passed them all. Ergo, FALSE if they have taken at lease one test and failed at least one. This way, whenever an employee takes a new test and the grade is recorded, the boolean can be updated if they ever fail. It should make the query you want simpler as well.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Quote Originally Posted by toadeny
    I am looking for a way in MS Access SQL to bring up the top 5 employees who have never failed a test, and if they have failed, they will not be shown on this list.
    Ok. This requires subqueries (FUN !!!)
    Code:
    SELECT TOP 5
      e.*,
      p.PassedTests,
      f.FailedTests
    FROM
      EMPLOYEE AS e
      INNER JOIN
        (SELECT e.EmployeeId, COUNT(h.TestID) AS PassedTests
        FROM EMPLOYEE AS e
          INNER JOIN HISTORY AS h ON e.EmployeeId = h.EmployeeId
        WHERE h.Passed = 1
        GROUP BY e.EmployeeId
        ) AS p ON e.EmployeeId = p.EmployeeId
      INNER JOIN
        (SELECT e.EmployeeId, COUNT(h.TestID) AS FailedTests
        FROM EMPLOYEE AS e
          INNER JOIN HISTORY AS h ON e.EmployeeId = h.EmployeeId
        WHERE h.Passed = 0
        GROUP BY e.EmployeeId
        ) AS f ON e.EmployeeId = f.EmployeeId
    WHERE f.FailedTests = 0
    ORDER BY p.PassedTests DESC
    The other one will be similar... you can figure it out, I'm sure?


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:01 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com