Possible Self Join - Extract based on dates 1

[IngDep]

Have a single MS Access table that includes the following fields;

EmpID---BeginDt---EndDt------Name
10235---1/1/08----12/31/08---Jill Smith
10235---1/1/09----12/31/09---Jill Smith
10236---6/1/08----12/31/08---Joe Smith
10237---1/1/09----12/31/09---Jane Smith

First objective is to extract all employees that have a End Date during Dec 2008 but not have a End Date during 2009.

Second objective is to extract all employees that have a End Date during 2009 that did not have an End Date during Dec 2008.

Is this possible? If so, how is it done?

My gut feeling is a query involving a left outer join...

Thanks in advance for any insight.

 

[PHV]

1)
SELECT * FROM yourTable
WHERE Format(EndDt,"yyyymm")="200812" AND EmpID Not In (SELECT EmpID FROM yourTable WHERE Year(EndDt)=2009)

2)
SELECT * FROM yourTable
WHERE Year(EndDt)=2009 AND EmpID Not In (SELECT EmpID FROM yourTable WHERE Format(EndDt,"yyyymm")="200812")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IngDep]

Will create the two queries.

It appears that a self join may not be necessary before the two queries are constructed and implemented.

When would a self join be required?