sql help

[bfreeman]

Hi all,

Trying to get this sql to work properly.

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!


Code:
--------------------

SELECT DISTINCTROW JobHours.Date
, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "." AS VolunteerName
, JobStatus.Status
, 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 (((Jobs.JobID)=6 Or (Jobs.JobID)=12 Or (Jobs.JobID)=13))
GROUP BY JobHours.Date, [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & "."
, JobStatus.Status
, Jobs.Job
, Jobs.Minimum
HAVING (((JobStatus.Status)="Active"
Or (JobStatus.Status)="Being Trained")
AND ((Sum(JobHours.Hours))<[Jobs].[Minimum]))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & ".";

--------------------

 

[Michel Walsh]

Hi,


Untested, but something like:


SELECT ...,
SUM( iif( JobId=6, Hours, 0 ) ) As TimeOnJobID6,
SUM(Hour) As TotalTime, ...
FROM ...
WHERE JobId IN( 6, 12, 13)
GROUP BY ...
HAVING SUM( iif( JobId=6, Hours, 0)) >=30
AND SUM(Hour) >= 40




sure, if you do not want to see the value for TimeOnJobID6, just not include
it in the SELECT clause, it is acceptable to have a criteria in the HAVING
clause involving data not in the SELECT clause, but sometimes "questionable"
(for who ever read the output)...


Hoping it may help,
Vanderghast, Access MVP