access 2000: query on two joins which returns records from one NOT contained in the other

  • Thread starter Thread starter sheree
  • Start date Start date
S

sheree

My two tables are:

Event (one)
-----
EID (autonum)
Symptom (text)

and

AEtoEvent (many)
-----
AEID
EID

I would like to create a query that includes all Events (EID, Symptom)
that DO NOT already appear in the AEtoEvent table. I'd be very happy
with either an SQL code example or instructions on how to do this in
design mode in access.
 
Try something along the lines of

SELECT eid, symptom
FROM Event
WHERE eid NOT IN (SELECT DISTINCT eid FROM AEtoEvent)

Hope This Helps
Gerald Stanley MCSD
 
Dear Sheree:

In the SQL View, you can code a LEFT JOIN, such as this:

FROM Event E
LEFT JOIN AEtoEvent A ON A.EID = E.EID

This will show all events, whether or not they have a row in AEtoEvent
or not. Those rows in Event that do not have a row in AEtoEvent will
show up once with all the columns coming from AEtoEvent having NULL
values. Assuming that AEtoEvent.EID is never null when there IS a row
in AEtoEvent, you can show only those rows of Event where there is no
corresponding row in AEtoEvent by filtering:

WHERE E.EID IS NULL

I think that will do what you want. This technique is sometimes
called a "Frustrated Outer Join."

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top