Queries

  • Thread starter Thread starter TN_VR_adminsec
  • Start date Start date
T

TN_VR_adminsec

I've been asked to run a query in the database my office maintains of
training and education backgrounds for each employee.
I know how to run basic queries but for the life of me cannot figure out how
to get the database to give me a list of employees who have NOT taken a
training course. Is there anyone who can point me in the right direction? I
can supply more details if needed.
 
The best way is to build a query of everyone that has taken the course
(include the primary key of the individuals) and then use that and the
table of employees to build an unmatched query using the Unmatched Query
Wizard found when you press the New button for a query.

You can do this all in one query IF your tablenames and fieldnames
consist of only letters, numbers, and underscore characters and none of
the table names are keyword.

For example

SELECT E.EmployeeID, E.EmployeeLast, E.EmployeeFirst
FROM Employees as E LEFT JOIN
(SELECT EmployeeID
FROM TableTraining
WHERE CourseNumber = "a12345") as CourseTaken
ON E.EmployeeID = CourseTaken.EmployeeID
WHERE CourseTaken.EmployeeID is Null



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I've been asked to run a query in the database my office maintains of
training and education backgrounds for each employee.
I know how to run basic queries but for the life of me cannot figure out how
to get the database to give me a list of employees who have NOT taken a
training course. Is there anyone who can point me in the right direction? I
can supply more details if needed.
Click to expand...

John's suggestion is certainly the most efficient, but the NOT EXISTS clause
may be a suitable alternative (the JET query optimizer doesn't necessarily
make this very efficient though!)

Something like

SELECT <whatever fields you want to see>
FROM Employees
WHERE NOT EXISTS
(SELECT EmployeeCourses.EmployeeID FROM EmployeeCourses
WHERE CourseID = Forms!MyForm!cboCourseToCheck
AND EmployeeCourses.EmployeeID = Employees.EmployeeID)

Adapt table and fieldnames of course.
 
Back
Top