Help With NOT EXISTS SubQuery

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ridgerunner - 03 Mar 2007 01:01 GMT

I tried for hours at work, today, to get this to work but I just do not
understand enough to determine how to write this in the query design window.
We have a table that contains employees' last name, first name and postion.
We have another table that contains employees' last name, first name and
required courses they have taken.

I have set a relation between the two tables on employees' first name and
last name and tried to set up the query to give us the names of store
managers that have not taken a certain course, using a query to gather all
store managers and a second subquery to give us the name of store managers
that have not taken a certain course. While I tried to make this work, I
received errors messages that "an ending bracket, parenthesis or bar is
missing" and another message about syntax errors or "the query can only
return one record" and sometimes the query runs but returns no records.

Can someone please help explain the exists syntax and what I might be doing
wrong?

Reply to this Message

John Spencer - 03 Mar 2007 17:46 GMT

Try two queries.

Query one identifies everyone that HAS taken the course. Save that query.

Query two uses unmatched query wizard to see who is in the employees
table and not in query one.

OR you can use a not exists query

SELECT E.LastName, E.FirstName
FROM Employees as E
WHERE Not Exists
(SELECT *
FROM Training as T
WHERE T.LastName = E.LastName and
T.FirstName = E.FirstName
and T.CourseID ="SomeCourseID")

IF you have a LOT of records the above could be slow. IF you have named
your fields and tables so they do not require square brackets around
then you should be able to use

SELECT E.LastName, E.FirstName
FROM Employees as E LEFT JOIN
(SELECT *
FROM Training
WHERE CourseID = "SomeCourseID") as T
ON T.LastName = E.LastName and
T.FirstName = E.FirstName
WHERE T.LastName is Null

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> I tried for hours at work, today, to get this to work but I just do not
> understand enough to determine how to write this in the query design window.
[quoted text clipped - 13 lines]
> Can someone please help explain the exists syntax and what I might be doing
> wrong?

Reply to this Message

ridgerunner - 04 Mar 2007 01:48 GMT

Thank you John. The first idea is a definite possibilty with the only
exception that a field in the query cannot be pulled in since it does not
exist in the database being matched, but that may not be a big issue.

How do you get the not exists query to run in the query design window? I
tried typing the data into a column but I get the same errors as before.

> Try two queries.
>
[quoted text clipped - 54 lines]
>> Can someone please help explain the exists syntax and what I might be
>> doing wrong?

Reply to this Message

John Spencer - 04 Mar 2007 14:36 GMT

If you are using the design view and not the SQL view then

Field: Exists(SELECT * FROM Training As T WHERE T.LastName = E.LastName
T.FirstName = E.FirstName and T.CourseID ="SomeCourseID")

Criteria: False

Also since I don't know your field types I guessed that CourseID is a
text field. If it is a number field then obviously you will want to
drop the quote marks around the value of the course.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

> Thank you John. The first idea is a definite possibilty with the only
> exception that a field in the query cannot be pulled in since it does not
[quoted text clipped - 61 lines]
>>> Can someone please help explain the exists syntax and what I might be
>>> doing wrong?

Reply to this Message