What the query is supposed to do is show individual volunteers that have not achieved minimum required hours for Bike Patrol. The must have a total of 40 hours, 30 of which must be classified as "Bike Patrol" (JobID=6) and the remaining 10 may be classified as either "Bike Patrol" (JobID=6), "Bike Patrol Special Events" (JobID=12), or "Bike Patrol Special Projects" (JobID=13).
I can get the query to filter the minimum hours for bike patrol, but cannot integrate the other criteria. Appreciate any help!
SELECT DISTINCTROW JobHours.Date
, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "." AS VolunteerName
, Sum(JobHours.Hours) AS SumOfHours
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 (((Jobs.JobID)=6 Or (Jobs.JobID)=12 Or (Jobs.JobID)=13))
GROUP BY JobHours.Date, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "."
Or (JobStatus.Status)="Being Trained")
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & ".";