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
>