[AccessD] Basic Query Design

On 6 Feb 2006 at 12:41, Jürgen Welz wrote:

> 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?
> 
> 

How about something like (aircode - may not run as is)

Select tblEmployee.EmployeeID, FirstName & ' ' & LastName  from
tblEmployee inner join 
(select distinct EmployeeID, Count(EmployeeID)
 from tblEmployeeCourse 
 where CourseID In (1,4,7) and Count(EmployeeID) = 3
Group By EmployeeID)
on tblEmploye.EmployeeID = tblEmployeeCourse.EmployeeID

That should return only the employees who have all three courses.
You can also get the employees who have done two of the three courses etc 
if you can't find anyone with all three :-)






-- 
Stuart