Return the next date recorded

[chrisaroundtown]

Hi,

I am having a problem where I cannot return the next date recorded against an employee. For example, I have the fields row_id, start_date, employee_id, team_id

I need a query to calculate the end_date for each record. The end_date would be the next recorded start_date for the same employee_id minus one. I cannot use the row_id as records may be added in the past.

I have tried using DLookup, DMax and DMin and can't get it to work. In effect, what I want is to return the minimum start_date where the start_date is greater than the start_date of the row, then minus one.

row_id start_date employee_id team_id
45 01/05/2005 78653 546
62 06/10/2006 78653 653
53 12/12/2007 78653 825

The end_date in row 1 here should be 05/10/2006, row 2 should be 11/12/2007 and as row 3 is the latest change the end_date would be blank.

This is not working for me:
DMin("[start_date]","[team_moves_tbl]","[start_date] > " & start_date)

Does anyone know a way to do this? Is there a problem using a greater than sign in the criteria?

Chris

 

[Golom]

Try this

Select A.Row_id, A.Start_Date, A.Employee_id, A.Team_id,

       (Select MIN(B.Start_Date) - 1 
        From [team_moves_tbl] As B
        Where B.Start_Date > A.Start_Date
          And B.Employee_id = A.Employee_id) As [End_Date]

From [team_moves_tbl] As A