How to Create an Exeception Report (1 Viewer)

[JHMarshIII]

Sorry all, I am barely a newbie programmer, I need help with structuring a complex query to drive an exception report.

Relationships and Background:
-----------------------------
I have four tables,

Buildings
Member-Volunteer
Jobs-Willing-To-Do
Job-Roles

Key related fields:

BuildingID
MemberID
JobID

Member-Volunteer is the main table that has a Building ID for each member that is related to a table of 100 Building addresses (Buildings) . This was to prevent redundant saving of the building addresses. This table is also related to the Jobs-Willing-To-Do table by MemberID. There is a further relationship of table Jobs-Willing-To-Do to the table Job-Roles (with fields, JobID and Job Description. So table Jobs-Willing-ToDo has only the field JobID and all the info like Job-Start-DT and Job-End-Date, but no Job Name, which is retrieved from Job-Roles by field JobID.

What I Need:
------------
1) From the perspective of the table Buildings I need to create a report of all 100 buildings that have Member-Volunteers that are found in Jobs-Willing-ToDo with a JobID of x, y, or Z and have a Job-Start-Dt that is Not Null. I need to also print not just the JobID code but the JobDescription as well which is in the look up table Job-Role.

2) Then I also need to create an exception report that isolates all the Buildings that don't have Member-Volunteers willing to serve a particular role (or) JobID (JobDesc). Or I guess NE to x, y, or z.

Is this done by using an nested SQL statement? Can someone lead me in the right direction?

Many thanks, John ...
---------------------------

Thanks UK for Your Support in The Gulf!
May All of our Soldiers Return Safely!

 

[FoFa]

Just off the top of my head, but try something like this:
Use this (may need to be tweaked a little) as the basis:
SELECT B.BuildingID, morecolumns
FROM Buildings AS B
INNER JOIN Member-Volunteer AS MV ON MV.BuildingID = B.BuildingID
INNER JOIN Jobs_Willing-To-Do AS JWTD ON JWTD.MemberID = MV.MemberID
INNER JOIN Job-Roles AS J ON J.JobID = JWTD.JobID
WHERE J.JobID in (x,y,z)
AND J.Job-Start_Date IS NOT NULL

For the Exception Report use:

SELECT B1.BuildingID
FROM Buildings AS B1
WHERE B1.BuildingID NOT IN (SELECT B.BuildingID
FROM Buildings AS B
INNER JOIN Member-Volunteer AS MV ON MV.BuildingID = B.BuildingID
INNER JOIN Jobs_Willing-To-Do AS JWTD ON JWTD.MemberID = MV.MemberID
INNER JOIN Job-Roles AS J ON J.JobID = JWTD.JobID
WHERE J.JobID in (x,y,z)
AND J.Job-Start_Date IS NOT NULL)

The subselect is the same SQL as the first one, it just excludes everything you had selected already. But it can only return one field to check against.