Peter does this works (I have no time to test it :o(((

SELECT Claims.Claim_Id, Claims2.FromDate, Claims2.Status;
       FROM Claims;
       LEFT JOIN Claims2 ON Claims.Claim_Id == Claims2.claims_ID
       WHERE BINTOC(Claims2.claims_ID)+DTOS(Claims2.FromDate) IN (SELECT BINTOC(Claims2.Claims_Id)+DTOS(MAX(Claims2.FromDate)) FROM Claims2);
       INTO CURSOR Temp

>the proble is that claims2_id if the PK of the child table so it wont give me the max(fromdate) for claim_id
>what I need is the max date, the status which goes with this date (and the PK claim2_id) for a given claim_ID
>
>
>
>
>
>>>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
>>
>>I think that might be something similar to this:
>>
>>

>>select claim2_id, max(fromdate) as LastDate;
>>  from claims2;
>>  group by claim2_id;
>>  into cursor Temp;
>>  nofilter
>>select Temp.*, Claims2.Status;
>>  from Temp join Claims2;
>>    on Temp.claim2_id = Claims2.claim2_id and Temp.LastDate = claims2.FromDate;
>>  into cursor Temp
>>