Database Systems Help

toadeny · May 21 '04, 05:43 AM (#1)

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

**Wired** · May 21 '04, 12:21 PM (#2)

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.

smoseley · May 21 '04, 01:47 PM (#3)

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?

Database Systems Help

Looking for the SQL Legend!