[AccessD] Basic Query Design

Stuart:

That certainly generates a shorter SQL string for the example raised, and of 
course, even more so as the number of course selections increases.  The 
concept of using a count in an aggregate query certainly returns the desired 
results without nesting a series of subqueries the way I was doing things.  
Following your lead, with 4 items picked from the list:

SELECT tblEmployeeCourse.EmployeeID, FirstName & ' ' & LastName FROM 
tblEmployeeCourse INNER JOIN tblEmployee ON tblEmployeeCourse.EmployeeID = 
tblEmployee.EmployeeID
WHERE CourseID In (1,2,4,5) GROUP BY tblEmployeeCourse.EmployeeID, FirstName 
& ' ' & LastName HAVING Count(tblEmployee.EmployeeID) = 4 ORDER BY FirstName 
& ' ' & LastName

This should run much faster, though speed hasn't been a problem with the 
1700 Employee records and 30 Course records (about 2500 junction table 
records). Thanks for pointing out the approach.

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





>From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
>
>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