Multiple NOT EXISTS query

[123451238]

I have a database in MS Access 2003 to do the regular operations for administration in my office. I need to make a query in SQL to find out which employees who have been working with all the projects involved with a certain building.

The relation is like this:

EMPLOYEE - PROJECTPARTICIPATION - PROJECT - BUILDING.

As far as I can see, this problem must be solved with a multiple EXISTS-query. The "BuildingID" (Primary key in BUILDING is in case =4.)

The definition of the table is like this (The primary keys are written in CAPITAL LETTERS):

Employee (EMPLOYEID, Name, ...)
Projectparticipation (EMPLOYEEID, PROJECTID, DATE)
Project (PROJECTID, BUILDINGID, StartDate, EndDate, Description, ...)
Building (BUILDINGID, Size, Height, ...)

How can I express this query in SQL in MS Access 2003?

Ole Helge

 

[PHV]

No need of EXITS clause, simply use JOIN.
In the query grid add the 4 tables and join each foreign key with the correspondind primary key, ie:
Employee.EMPLOYEID ---> Projectparticipation.EMPLOYEEID
Projectparticipation.PROJECTID ---> Project.PROJECTID
Project.BUILDINGID ---> Building.BUILDINGID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Lothario]

Something like this:

SELECT Employee.EmployeeID, Name, ProjectId, ProjectDate,BuildingID, StartDate, EndDate, Description FROM
Employee INNER JOIN Projectparticipation
ON Employee.EmployeeId=Projectparticipation.ProjectId
INNER JOIN Project
ON Projectparticipation.ProjectID=Project.ProjectId
INNER JOIN Building
ON Project.BuildingID= Building.BuildingId
WHERE Building.BuildingID= "&yourvariable&"

-L