Results 1 to 7 of 7

Thread: sql help

  1. #1
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Angry Unanswered: sql help

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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75
    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.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75
    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] & ".";

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmmm, it wouldn't be the comparison between the string "jobID" and the number 6, would it?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2004
    Location
    OHIO
    Posts
    75

    Cool

    Originally posted by r937
    hmmm, it wouldn't be the comparison between the string "jobID" and the number 6, would it?

    gotcha!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •