Query Help

[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

 

[Guest]

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

 

[Brendan Reynolds]

SELECT Employees.DatabaseID FROM Employees WHERE Employees.DatabaseID NOT IN
(SELECT EmployeesReviews.DatabaseID FROM EmployeesReviews WHERE DateOfReview