Value from next row (1 Viewer)

[aqif]

Hi

Lets look at my data

O_ID PersonID StYear EnYear
1 1 1986
2 1 1989
3 1 1992
4 3 1987
5 4 1990
6 4 1996

What I want to do is that the Start Year (StYear) value from the next row for every person should be inserted as End year in the previous row. Then the data wil look like this

O_ID PersonID StYear EnYear
1 1 1986 1989
2 1 1989 1992
3 1 1992 *
4 3 1987 *
5 4 1990 1996
6 4 1996 *

It should not touch the last row for that person and also leave the end year with * sign indicating that this is the current job as there are no records after that. I have tried to create a query of distinct persons and then run a loop but cannot seem to fix the problem.

Any help will be appreciated

 

[WayneRyan]

agif,

I don't understand what we are trying to do at
all, but this will do it ...

Dim dbs As Database
Dim rst As Recordset
Dim sql As String
Dim LastID As Number
Dim LastDate As String

LastID = 0
LastDate = ""
sql = "Select * From YourTable Order By PersonID, StDate"

Set rst = dbs.OpenRecordSet(sql)
While Not rst.EOF and Not rst.BOF
If LastID <> rst!PersonID And LastID <> 0 Then
rst.MovePrevious
rst.Edit
rst!EndDate = "*"
rst.Update
rst.MoveNext
LastID = rst!PersonID
LastDate = rst!StartDate
ElseIf LastID <> rst!PersonID
rst.MovePrevious
rst.Edit
rst!EndDate = "*"
rst.Update
rst.MoveNext
ElseIf LastID = rst!PersonID
rst.MovePrevious
rst.Edit
rst!EndDate = LastDate
rst.Update
rst.MoveNext
LastDate = rst!StartDate
End If
rst.MoveNext
Wend

hth,
Wayne