Find Unmatched Data for several periods of time

[Heather]

Hi, I'm trying to calculate attrition data. I have one table with all
employees at specific dates in time (beginning of each month).

I've written several queries to find the unmatched people between month 1
and month 2 but it seems like there must be an easier way to do this than by
running the queries over and over again for each month that i need to find...

So here's what I've got now. I'm SURE this is the absolute worst way to do
this, but it's all i could figure out w/ my limited access skills... help?

Query 1: Gets all employees from Begin Date (month1)
Query 2: Gets all employees from End Date (month2)
Query 3: Finds unmatched employees.

I've currently got query 3 appending a new table with these unmatched
employees and the date.

Then I'm running the queries over and over and over....

HELP?!?

 

[John Spencer]

I think the following should work as a starting point.

SELECT A.EmployeeID, A.BeginDate
, IIF(B.BeginDate is Null,"GONE BABY",Null) as EmployeeLeft
FROM Employees as A LEFT JOIN Employees as B
ON A.EmployeeID = B.EmployeeID
AND A.BeginDate = DateAdd("m",1,B.BeginDate)



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[louisjohnphillips]

I think the following should work as a starting point.

SELECT A.EmployeeID, A.BeginDate
, IIF(B.BeginDate is Null,"GONE BABY",Null) as EmployeeLeft
FROM Employees as A LEFT JOIN Employees as B
ON A.EmployeeID = B.EmployeeID
AND A.BeginDate = DateAdd("m",1,B.BeginDate)

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================










- Show quoted text -

A twist on the previous example might be of further help.


The first day of the month can be found by using the DatePart function

For example,

SELECT PayRoll.EmployeeID,
1 + PayRoll.DatePaid - DatePart( "d", PayRoll.DatePaid ) as
PayMonth
from PayRoll
group by PayRoll.EmployeeID, 1 + PayRoll.DatePaid - DatePart( "d",
PayRoll.DatePaid )

Let's call this query EmployeesOfMonth. It reports who was on the
payroll during each month, one row per employee per month, no matter
how many paydays there were in the month.

To identify those EmployeeIDs that dropped off in a month, one might
query:

SELECT A.EmployeeID, A.PayMonth
from EmployeesOfMonth as A
where not exists
( select 'true'
from EmployeesOfMonth
where EmployeeID = A.EmployeeID
and PayMonth = DateAdd( "m", 1, A.PayMonth )

 

[Heather]

Sorry, I think I was unclear with my problem. I'm trying to find a way that
I can do this without having to type in the Beginning Date and End Date each
time I run the query and instead have it run for every date in my table. (The
table has one date's worth of data per month)

 

[Heather]

Nevermind... This worked great!!! THANK YOU!!!!!!