sql help

ironchef · #1 (**permalink**) 01-13-04, 13:44

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] & ".";

r937 · #2 (**permalink**) 01-13-04, 15:08

moderator, please move to Microsoft Access forum

ironchef, just add the additional conditions to the HAVING clause

the tricky part is that the conditions involve "partial sums" based on the jobID

first, move the Status conditions out of HAVING and into the WHERE clause

then your HAVING clause becomes:

Code:

having sum(JobHours.Hours) > [Jobs].[Minimum]  
   and sum(iif(JobID=6,JobHours.Hours,0)) > 30

i'm assuming that the Minimum field is what you were referring to with the 40 hours

note also that i turned the inequality for minimum hours around -- you want people who have more than the minimum, not less

ironchef · #3 (**permalink**) 01-13-04, 15:14

Quote:

Originally posted by r937
first, move the Status conditions out of HAVING and into the WHERE clause

then your HAVING clause becomes:

Code:

having sum(JobHours.Hours) > [Jobs].[Minimum]  
   and sum(iif(JobID=6,JobHours.Hours,0)) > 30

i'm assuming that the Minimum field is what you were referring to with the 40 hours

note also that i turned the inequality for minimum hours around -- you want people who have more than the minimum, not less

So, basically you are saying just change the "having" to "where" and the code you have written should work for the criteria i have stated?

thanks.

r937 · #4 (**permalink**) 01-13-04, 16:04

no, don't change HAVING to WHERE

move some of the stuff in the HAVING to WHERE, but leave the other stuff, and add the new stuff

whether it will work or not then depends on your data, doesn't it? i mean, that part about the 40 hours and the reversal of the inequality...

ironchef · #5 (**permalink**) 01-13-04, 16:06

Made the change suggested and it gives me an error that says, "Data type mismatch is criteria expression"

here's the code...

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 (((Sum(JobHours.Hours))>[Jobs].[Minimum] 
And Sum(IIf("JobID"=6,[JobHours].[Hours],0))>30))
ORDER BY [LastName] & ", " & [FirstName] & " " & [MiddleInitial] & ".";