thanks Tamar.

Peter



>>I have the following tables
>>
>>claims.dbf
>>claim_id I
>>....
>>
>>
>>claims2.dbf
>>claim2_id I
>>claims_ID I
>>fromdate D
>>status C(10)
>>....
>>
>>
>>for each claims.claim_id I need the max(claims2.fromdate) and the associated status for this date for a given data of ldDate
>>
>>meaning: what was the status of claims 123 ... 456 on 06/30/2003 for example
>>
>
>In VFP 9, you can do something like:
>
>SELECT Claims.Claim_ID, MaxClaim.FromDate, Claims2.Status ;
> FROM Claims ;
> JOIN (SELECT Claims_ID, MAX(FromDate) AS FromDate ;
> FROM Claims2 ;
> GROUP BY Claims_ID) MaxClaim ;
> ON Claims.Claim_ID = MaxClaim.Claims_ID ;
> JOIN Claims2 ;
> ON Claims2.Claims_ID = MaxClaim.Claims_ID ;
> AND Claims2.FromDate = MaxClaim.FromDate
>
>You may have to tweak that a bit, but it should give you the idea.
>
>Tamar