[AccessD] Basic Query Design

Hey Steve:

The actual query is a bit more complex than posted as it takes into account 
certification expiry dates and may mix in numerous additional Inclusive and 
Exclusionary conditions, several driven by multi select list boxes (1 
through 8 regional offices, 1 or more of several categories of employee...). 
  The question I posed was to address one aspect of a search that I provide 
for every major form.  I won't have a problem with dupliicate courses in my 
'count'.  If I mapped bit flags to categories I'd have no trouble 
dynamically setting query by join SQL and denormalizing everything to the 
Employee table with a few external lookups.  But I digress.

Getting back to how one would integrate this with a SQL Server BE; the SQL 
string rowsource for a hit list may join anywhere from 0 to 8 additional 
tables in any combination, an In(list) may require a list of values subquery 
or nested subqueries and require in/exclusive conditions on the main table 
or any of the related tables.  How does anyone to something like this 
without resorting to dynamic SQL?  Do you have 60 stored parameter stored 
procedures that you substitute in as the rowsource as required?

Stuart's posted solution provides a reasonable approach to cleaning up the 
string generation code I have been using to find matches for meeting 'every 
one' in a list on a single field.  I have yet to run some performance tests 
but expect to do so over the next couple of days.

Anyone else have an idea of another way of doing this before I run some 
testing?

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



>From: Steve Erbach <erbachs at gmail.com>
>
>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.