[AccessD] Unmatched query problem

Hi Joseph

That could be something like this:

<SQL>

  PARAMETERS
    CollegeID Long;
  SELECT
    [CollegeID] AS CollegeID,
    ProgramID
  FROM
    tblPrograms
  WHERE
    ProgramID Not In
      (Select
        ProgramID
      From
        tblOffered As T
      Where
        T.CollegeID = [CollegeID];);

</SQL>

However, for large tables this may run slower than your two saved
queries.

/gustav


> Cross posted to Access-L and AccessD

> I have a table that contains a list of colleges, a second table that
> contains a list of programs. Each of these tables has an autonumber ID.  A
> third table contains a list of programs offered by each college.   For a
> given college ID, I need a query to return a list of all programs that are
> not offered by the college.

> College table
> College ID  College Name
>     1                   State U
>     2                   Private U
>     3                  Out-of-state U

> Program table
> Prog ID Program Name
>     1         Economics
>     2         Math
>     3         English
>     4         Foreign Language
>     5         Engineering

> Programs offered by each college
> College ID       Prog ID
>    1                      1
>    1                      3
>    1                      5
>    2                      1
>    2                      2
>    3                      2
>    3                      3

> The results for college 1 would be:
> Program ID         Program
>     2                        Math
>     4                        Foreign Language

> How do I create an unmatched query, selecting all records from the Program
> table that do not have an entry in the programs offered table for a given
> college ID?

> I have been able to accomplish this with a saved query that selects just the
> entries from the programs offered table for the desired college, and then
> using this query as the right side of the join to select the unmatched
> entries, but I would like to accomplish this without the saved query.  The
> saved query is:
> SELECT tblOffered.*
> FROM tblOffered
> WHERE (((tblOffered.lngOfferedColllegeID)=1));

> And the unmatched query is:
> SELECT tblProgram.*, qryStep1.lngOfferedColllegeID
> FROM tblProgram LEFT JOIN qryStep1 ON tblProgram.atnProgramID =
> qryStep1.lngOfferedProgramID
> WHERE (((qryStep1.lngOfferedColllegeID) Is Null));

> What is the proper syntax to combine these into a single query?

> Joe O'Connell