Check previous record when promote

[learning_codes]

Hi,

I notice that I have two records that show the person got promoted to
a new job and previous job. Is there a way for me to collapse into
one records instead of two reocrds by employee ID.


ID Position Start Date End Date
Reason
222 Support Clerk 01/02/03 03/03/03
Promotion
222 Filing Clerk 03/04/03 12/31/9999
New Employee


I want to get from two into one line record on query:
ID 222
Position Support Clerk
Start Date 03/04/03
End Date 12/31/9999
Reason Promotion
Prev_Position Support Clerk
Prev_Start Date 01/02/03
Prev_End Date 03/03/03

I would appreciated if you can help me out much appreciated.
Thanks

 

[Tom van Stiphout]

On Wed, 18 Feb 2009 17:05:31 -0800 (PST), "(e-mail address removed)"

Why stop at two? A person could be promoted more than once. I think
you may want to build a report to show the promotion history of an
employee.

Anyway, to answer your more limited question, here is a starting
point:
SELECT myTable.ID, myTable.Position, myTable.StartDate,
myTable.EndDate, myTable.Reason, T2.Position AS PrevPosition,
T2.StartDate AS PrevStartDate, T2.EndDate AS PrevEndDate
FROM myTable INNER JOIN myTable AS T2 ON myTable.ID = T2.ID
WHERE (((myTable.StartDate)=(select max(StartDate) from myTable as T2
where T2.ID=myTable.ID)) AND
([T2].[StartDate]<[myTable].[StartDate]));

(replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[learning_codes]

On Wed, 18 Feb 2009 17:05:31 -0800 (PST), "(e-mail address removed)"


Why stop at two? A person could be promoted more than once. I think
you may want to build a report to show the promotion history of an
employee.

Anyway, to answer your more limited question, here is a starting
point:
SELECT myTable.ID, myTable.Position, myTable.StartDate,
myTable.EndDate, myTable.Reason, T2.Position AS PrevPosition,
T2.StartDate AS PrevStartDate, T2.EndDate AS PrevEndDate
FROM myTable INNER JOIN myTable AS T2 ON myTable.ID = T2.ID
WHERE (((myTable.StartDate)=(select max(StartDate) from myTable as T2
where T2.ID=myTable.ID)) AND
([T2].[StartDate]<[myTable].[StartDate]));

(replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

Hi,

I notice that I have two records that show the person got promoted to
a new job and previous job.  Is there a way for me to collapse into
one records instead of two reocrds by employee ID.

ID            Position             Start Date       End Date
Reason
222          Support Clerk     01/02/03         03/03/03
Promotion
222          Filing Clerk         03/04/03        12/31/9999
New Employee

I want to get from two into one line record on query:
ID                       222
Position              Support Clerk
Start Date           03/04/03
End Date            12/31/9999
Reason               Promotion
Prev_Position      Support Clerk
Prev_Start Date   01/02/03
Prev_End Date    03/03/03

I would appreciated if you can help me out much appreciated.
Thanks- Hide quoted text -

- Show quoted text -

Thank you and I tried and it looks great but need to analyse more .

I have one problem for me because the data is little messy that I need
to clean up easily.


EmpID JobID StartDate EndDate 2MosMoreInd
1 2222 676 01/01/08 01/31/08 Blank
2 2222 676 02/01/08 06/30/08 Y
3 2222 676 09/15/08 10/01/08 Blank
4 2222 676 12/15/08 02/09/09 Y

I'm looking for one record that collapse #1 and #2 because of one day
apart between #1 (End date) and #2 (StartDate).
#1 and #2 should show continue (Y) instead of #1 show 2MosMoreInd as
blank.

I'm interested to see #1 and #2 should be :

EmpID JobID StartDate EndDate 2MosMoreInd
2222 676 01/01/08 06/30/08 Blank
(Grouped #1 and #2)
2222 676 09/15/08 10/01/08 Blank
(No change)
2222 676 12/15/08 02/09/09
Y (No change)

I would be appreciated if you provide me some help.

Thanks