>Hi,
>I have records as below
>
>Name Date Action
>--------------------------------
>John 01/01/2005 Y
>John 01/03/2005 T
>John 01/04/2005 F
>
>I need to transform these records to :
>
>Name Date Date2 Action
>---------------------------------------------
>John 01/01/2005 28/02/2005 Y
>John 01/03/2005 31/03/2005 T
>John 01/04/2005 / / F
>
>
>The date of next record will be the date2 of current record.
>
In VFP 9, you could do something like this:
SELECT Name, Date AS Date1, ;
(SELECT MIN(Date) FROM Table T2 ;
WHERE T2.Name = Table.Name ;
AND T2.Date > Table.Date ;
AND T2.Action = Table.Action) AS Date2, ;
Action ;
FROM Table ;
INTO CURSOR NewTable
You'll probably have to tweak this, but it should give you the idea.
Tamar