[AccessD] Basic Query Design

Jürgen,

» How does anyone do 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? «

A fair question.  My hope was to find a set-oriented approach like the
old Paradox queries I mentioned.  There the concept of a SET of
records meeting several conditions (in your case, several different
courses) is built into the query by example system.  To tell you the
truth, I used Paradox first to create a query that would do what you
asked for using data I have that has similar relationships.  The query
took up very little space and modifying it dynamically would be a
piece of cake.  I had hoped that Paradox's ability to generate a SQL
version of the query would help me answer your question.  Alas! 
Paradox had no way to translate its native SET operations into SQL
code.  There is no comparable capability.

Thus Stuart's solution looks to be the most compact and elegant one.

I've read some of your posts about bit flags so your digression was
not unexpected nor unwelcome.

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security


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