Query Help

  • Thread starter Thread starter scadav
  • Start date Start date
S

scadav

Folks

Sorry, I am not an access expert and this may be simple for someone,
but I have been banging my head against a wall.

I need some help writing a query in access. I have two tables:

Employees
EmployeesReviews

They are linked together by an Employee ID. The Reviews table has a
DateOfReview, ReviewComments, etc.

I am trying to get a list of all employees who have not had a review
after a certain date. Every time I write the query using:

SELECT Employees.DatabaseID FROM Employees INNER JOIN EmployeesReviews
ON Employees.DatabaseID=EmployeesReviews.DatabaseID WHERE
DateOfReview<=#3/1/2006#

it just shows me a list of all the people that have reviews before that
day, but it is possible that the employee had a review after that day.
Additionally if someone has never had a review, this query will not
include those.

Can someone help me with this?

Thanks
 
Yor getting a list of all employees before that date because you havent
included the "Not" case.
Try:

SELECT Employees.DatabaseID FROM Employees INNER JOIN EmployeesReviews
ON Employees.DatabaseID=EmployeesReviews.DatabaseID WHERE
DateOfReview Is Not <=#3/1/2006#

That will give you a list of all those who have not had a review after
3/1/2006.
As for those who have never had a review, maybe you could add a new field
onto the emloyee table (Yes/No), and set this to True if their EmployeeID
exists in the EmployeeReview table.

If you need further explanation let me know, I'm sure I can help or, if not
someone else on here will sure be able to give you the simplest way of doing
this task.

HTH
 
SELECT Employees.DatabaseID FROM Employees WHERE Employees.DatabaseID NOT IN
(SELECT EmployeesReviews.DatabaseID FROM EmployeesReviews WHERE DateOfReview
 
Back
Top