[AccessD] Basic Query Design

I'm drawing a blank today so I thought I'd throw out a question.

I provide search forms allowing users to filter data on multiple criteria 
and they work fine but, I can't help but think there is something I am 
missing.  For example, I have an Employee table and a table of Courses taken 
by Employees that serves as a junction between the Employee table and a 
table of Course.

Among other things, my search form provides a multiselect listbox with a 
hidden 1st column (CourseID) and a visible 2nd column (CourseName) where a 
user may select one or more (or no) course.  As a user selects or unselects 
a course, a list box of search hits is populated with a hidden 1st column 
(EmployeeID) and a concatenated 2nd column (FirstName & ' ' & LastName).  
The purpose of the search is to provide a user a list of employees who have 
a particular combination of certifications.  A typical scenario is that we 
would like to find an employee who has taken Suncor/Syncrude Orientation, 
has First Aid and Boom Forklift certifications.

The code generates a series of subqueries and dynamically sets the hit list 
rowsource SQL, but I can't help but think there must be an easier way.  Here 
is the SQL for the example (without restrictions on Employee category, 
union/non-union, employiing division, regional office, and skipping IsActive 
and restrictions on Deleted courses and Employees...........)

SELECT tblEmployee.EmployeeID, FirstName & ' ' & LastName FROM tblEmployee 
WHERE tblEmployee.EmployeeID In (Select tblEmployee.EmployeeID FROM 
tblEmployee INNER JOIN tblEmployeeCourse ON tblEmployee.EmployeeID = 
tblEmployeeCourse.EmployeeID Where tblEmployee.EmployeeID In (Select 
tblEmployee.EmployeeID FROM tblEmployee INNER JOIN tblEmployeeCourse ON 
tblEmployee.EmployeeID = tblEmployeeCourse.EmployeeID Where 
tblEmployee.EmployeeID In (Select tblEmployee.EmployeeID FROM tblEmployee 
INNER JOIN tblEmployeeCourse ON tblEmployee.EmployeeID = 
tblEmployeeCourse.EmployeeID Where CourseID = 14) And CourseID = 1) And 
CourseID = 12) ORDER BY FirstName & ' ' & LastName

There is nothing wrong with the way it currently works but I can't help but 
think that there is a better way to do this, especially should we port to a 
SQL Server BE.  Surely there is a better way to pass 'AND' (Exclusive) 
parameters along the lines of an 'OR' (Inclusive ie, IN(list) style) 
parameter.  Anyone?


Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com