Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: Help reversing a query

    I have two tables. The first table holds a list of employee names. The second table holds reviews done for these employees. Each employee will have at least one review done per week. I need a query to return all the employee names that haven't had a review done for the current week. I'm passing the start date and the end date for the current week. Also, I need to only select the names for the current time period. Meaning, I don't want to get all the names because the table is filled with reviews that were done all year long and they don't fall into the date range I'm using.

    Here is what I've come up with.

    Code:
    SELECT Emp.EmpName
    FROM Emp INNER JOIN Reviews ON Emp.EmpName = Reviews.RevEmpName
    WHERE (((Reviews.RevDate) Between #12/11/2011# And #12/17/2011#));
    As you can see, I'm not even close. This is showing me the oposite of what I'm looking for. It's showing me a list of EmpNames that have reviews done in the date range. So I need the reverse of this query.

    Thanks for you time.
    Jim

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Emp.EmpName
      FROM Emp 
    LEFT OUTER 
      JOIN Reviews 
        ON Reviews.RevEmpName = Emp.EmpName
       AND Reviews.RevDate BETWEEN #12/11/2011# AND #12/17/2011#
     WHERE Reviews.RevEmpName IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2009
    Posts
    50
    Just tried that out and I'm getting an error:
    Between operator without And in query expression ".

    Not sure where thats coming from. I copied your code and pasted it in to Access directly. I also tried removing the between and replacing it with >= and <=. Sadly that didn't work either. I tried putting double and single quotes around the date values, still getting the error. I also tried incasing everything between ( and ) and then tried changing AND to And. Still not working.

    Have I done something wrong? or am I missing something?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    suspect
    SELECT Emp.EmpName
    FROM Emp
    LEFT OUTER
    JOIN Reviews
    ON Reviews.RevEmpName = Emp.EmpName
    AND Reviews.RevDate BETWEEN #12/11/2011# AND #12/17/2011#
    WHERE Reviews.RevEmpName IS NULL
    should read
    SELECT Emp.EmpName
    FROM Emp
    LEFT OUTER
    JOIN Reviews
    ON Reviews.RevEmpName = Emp.EmpName
    WHERE Reviews.RevEmpName IS NULL
    AND Reviews.RevDate BETWEEN #12/11/2011# AND #12/17/2011#
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, healdem, if you put the date condition in the WHERE clause, you actually negate the effect of the outer join

    for an unmatched row from the left table, the columns that would've come from the right table are all set to NULL, and as you know, NULL isn't equal to anything, so it can't be between those dates, so your query returns no rows at all

    i suspect it's just ms access having another hissy fit because it doesn't understand compound conditions unless you parenthesize them
    Code:
    SELECT Emp.EmpName
      FROM Emp 
    LEFT OUTER 
      JOIN Reviews 
        ON (
           Reviews.RevEmpName = Emp.EmpName
       AND Reviews.RevDate BETWEEN #12/11/2011# AND #12/17/2011#
           )
     WHERE Reviews.RevEmpName IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2009
    Posts
    50
    That worked perfectly. Thanks everyone for the assistance. You all deserve a beer on me.

    Thanks
    Jim

Posting Permissions

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