Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that prompts the user to enter a specific type of data.
i.e please enter a training type : (I Enter) *Fire*.

What should happen is I get a lsit of all people whom have not had any fire
training however I get a couple of anomolies come up, the results include
people who have had the training.

SELECT DISTINCT Tbl_Training_Done.[Clock No], Tbl_Employees.Firstname,
Tbl_1_Employees.Surname, Tbl_1_Employees.Job, Tbl_1_Employees.Location
FROM Tbl_1_Employees INNER JOIN Tbl_Training_Done ON Tbl_1_Employees.[Clock
No] = Tbl_Training_Done.[Clock No]
WHERE (((Tbl_Training_Done.Training) Not Like [Enter Training Name or part
ie *Fire*:]))
GROUP BY Tbl_Training_Done.[Clock No], Tbl_1_Employees.Firstname,
Tbl_1_Employees.Surname, Tbl_1_Employees.Job, Tbl_1_Employees.Location;

I believe what is happening is that it selects all relevant people it simply
does not include any training with fire in, but what i need is to get it to
show employees who do not have said training.

help me if u can i'm feeling down.........need coffee.......
 
So you want the people that have NOT had the training at all. Easiest way to do
this is to identify those that have had the training and then use that to get
those who are not in that group.

Two solutions.

SELECT E.*
FROM TblEmployees as E
WHERE E.[Clock No] NOT IN
(SELECT T.[Clock No]
FROM Tbl_Training_Done as T
WHERE T.Training Like [Enter Training Name])

That will probably be slow. So you may need to resort to a two-query solution.
QueryA
SELECT T.[Clock No]
FROM Tbl_Training_Done as T
WHERE T.Training Like [Enter Training Name]

QueryB uses the saved QueryA
SELECT E.*
FROM TblEmployees as E LEFT JOIN QueryA as A
 
Back
Top