[AccessD] Basic Query Design

Steve Erbach erbachs at gmail.com
Mon Feb 6 20:06:43 CST 2006


Jürgen,

I've been working on this off-and-on today.  Your query reminded me
that a database I used to work with, Paradox, contained set operations
for this very thing:  Which employees have taken every one of the
following list of courses?

I thought at first that the ALL keyword might do the trick in SQL, but
no such luck.  It doesn't look like there's a straightforward
set-oriented way to perform this query in SQL.  It would have to be
something like what Stuart showed you.  Only thing I wonder about with
that query is if an employee took one of the courses more than once
but skipped one of the ones you're looking for.  The COUNT would be
the same, but your criteria would not be met.

Steve Erbach
Neenah, WI

On 2/6/06, Jürgen Welz <jwelz at hotmail.com> wrote:
> 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.



More information about the AccessD mailing list