Automated transaction grouping and numbering

[Ruth]

I have been asked to help a co-worker automate a data review task. I
am working in Access 2003. We have a query output in Excel (from our
mainframe) that lists employees with the following fields:
SSN, EmplName, ActionCd, ActionEffDt

Action codes (ActionCd) include Hire, Term and Rehire.

The objective is to identify employees who rehired within 365 days of
their termination. We have some people who have termed and been
rehired up to 5 or 6 times (seasonal workers, perhaps?)

Here's how I am currently calculating. I assigned all "Hire" Action
Codes a TxNo of "1". I sorted remaining transactions ascending by
date. I manually entered 'pairs' of TxNo's for the terms and rehires.

TxNo SSN EmplName ActionCd ActionEffDt
1 111223333 DOE JOHN M HIRE 22-Jan-01
2 111223333 DOE JOHN M TERM 30-Jan-01
2 111223333 DOE JOHN M REHIRE 06-Mar-01
3 111223333 DOE JOHN M TERM 16-Mar-01
3 111223333 DOE JOHN M REHIRE 07-Jun-04
4 111223333 DOE JOHN M TERM 15-Jun-04
4 111223333 DOE JOHN M REHIRE 20-Aug-07
5 111223333 DOE JOHN M TERM 29-Aug-07

I created queries for each transaction 'pair' and calculated number of
days from termination to rehire. Assembled Txn query results in a
query so that all transaction results would show on a single row for
an individual.

(This didn't paste too well, but maybe you can get the idea... the
first full line would be the column headings, the second full line is
how the data shown above would display and calculate.)
SSN Employee Name Term2Date Rehire2Date Days2Diff Date2Diff Term3Date
Rehire3Date Days3Diff Date3Diff Term4Date Rehire4Date Days4Diff
Date4Diff
111223333 DOE JOHN M 1/30/2001 3/6/2001 35 Within 12 months 3/16/2001
6/7/2004 1179 N/A 6/15/2004 8/20/2007 1161 N/A

The problem with this solution is that it requires manual entry of the
"Transaction pair numbers" and we have too many records for this to be
a viable solution. Can anyone offer a possible solution for automating
either assigning numbers to the transactions so that I can group them
OR another way to automate this process?

Thanks in advance!

 

[Allen Browne]

So the goal is to select all records of re-hirings where the person
previously left within 365 days.

You could use a subquery to examine the same table for the records where
TxNo is other than 1, it's the same employee, and the date is in the
previous year.

This is just an example, but hopefully sets you on the track:
SELECT * FROM Table1
WHERE TxNo = 1
AND EXISTS
(SELECT Max(ActionEffDt) AS MaxDt
FROM Table1 AS Dupe
WHERE Dupe.TxNo <> 1
AND Dupe.SSN = Table1.SSN
AND Table1.ActionEffDt - Dupe.ActionEffDt
Betwen 1 And 365);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html#AnotherRecord