I'm trying to write a SQL statement that will do the following:

Need to find the minimum hours for all the job categories of Bike Patrol (BP), Bike Patrol Special Events (BPSE), and Bike Patrol Special Projects (BPSP) is a cumulative total of 40 hours; 30 of which must be classified as "Bike Patrol". There is no minimum hours for BPSE or BPSP. People can choose to do all of their hours exlusively in BP and not have any hours in BPSE and BPSP. However, if they want to participate in BPSE and BPSP, they must have at least 30 of their hours in just BP.

I need a query that shows any volunteers where the cumulative total of hours for BP, BPSE and BPSP adds up to less than 40 hours. I also need to know of any volunteers that recorded less than 30 hours in just the BP category (without adding BPSE and BPSP), regardless of whether their cumulative total hours are greater than 40 or not. Ideally I'd like a list in alphabetical order of all these people so we can notify them that they failed to meet the minimum requirements of the program. And I'd like the list to show their hours in each category so when I contact people I can reference their hours. This might need to be broken up into two different queries, that's fine....

If you do need to make it two reports, then perhaps make the 2nd report show all volunteers that recorded less than 30 hours in just the BP category (without adding BPSE and BPSP), but that have recorded a cumulative total of 40 or more hours and that would avoid duplicating names on the second report.

Also, the query(s) need to pull names of anyone that has recorded zero hours.

I have provided my existing code...but as you will see i'm not getting what I need.

Code:
SELECT DISTINCTROW JobHours.Date
, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "." AS VolunteerName
, Jobs.Job
, Jobs.Minimum
, Sum(JobHours.Hours) AS SumOfHours
FROM Volunteers 
INNER JOIN ((Jobs 
INNER JOIN (JobStatus 
INNER JOIN VolunteerJobs 
ON JobStatus.JobStatusID = VolunteerJobs.JobStatusID) 
ON Jobs.JobID = VolunteerJobs.JobID) 
INNER JOIN JobHours 
ON VolunteerJobs.VolunteerJobID = JobHours.VolunteerJobID) 
ON Volunteers.VolunteerID = VolunteerJobs.VolunteerID
WHERE (((JobStatus.Status)="Active" 
Or (JobStatus.Status)="Being Trained") 
AND ((Jobs.JobID)=6 
Or (Jobs.JobID)=12 
Or (Jobs.JobID)=13))
GROUP BY JobHours.Date, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "."
, Jobs.Job
, Jobs.Minimum
HAVING (((JobHours.Date)>=[forms]![Report Date Range]![BeginDate] 
And (JobHours.Date)<=[forms]![Report Date Range]![EndDate]) 
AND ((Sum(JobHours.Hours))<[Jobs].[Minimum]) 
AND ((Sum(IIf([Jobs].[JobID]=6,[JobHours].[Hours],0)))<30) 
AND ((Sum(IIf([Jobs].[JobID]=12,[JobHours].[Hours],0)))<30) 
AND ((Sum(IIf([Jobs].[JobID]=13,[JobHours].[Hours],0)))<30))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & ".";
The JobID = 6 is Bike Patrol (BP)
The JobID = 12 is Bike Patrol Special Event (BPSE)
The JobID = 13 is Bike Patrol Special Projects (BPSP)

Thanks in advanced.

BAF