Cannot get same-table join to work

[yunno_who]

Hi there

I don't seem to want to do anything that's very different to the host
of examples of joing a table to itself that I've found in this
newsgroup, but obviously I'm missing something.

I have a table that contains employee records for every month of the
year. I'm simply trying to find out the IDs of those employees who
left, for example, between May and June.

I have managed to get it working as separate queries, but need to get
it into 1 query to work via VBA.

This is as far as I've got:

SELECT
(SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=5))) AS Month_5
FROM (SELECT Aggregated_2006_YTD_Salary_Subset_Table.Employee_ID
FROM Aggregated_2006_YTD_Salary_Subset_Table
WHERE (((Month([Update_Date]))=6))) AS Month_6
RIGHT JOIN Month_5 ON Month_6.Employee_ID = Month_5.Employee_ID
WHERE (((Month_6.Employee_ID) Is Null));

I would much appreciate any help you can give.

 

[David Cox]

In a hurry:-

I would simply do a group query to get maximum date for each employee and
use criteria between ....

 

[yunno_who]

I'm sorry - your answer is a little too cryptic for my brain - could
you elucidate a little further please?