>>I'm thinking I can inner join to (select max(iSequence_Number)...) in my additional join statement. Do you know if we have a limitation for number of projections? BTW, I can not figure out how to write this inner select either.
>>
>
>
>this is the SQL syntax, but VFP9 don't support ON subquery
>

> INNER JOIN Trans_Insurances on ;
>	 Trans.cTrans_pk = Trans_Insurances.cTrans_fk ;
>         AND  Trans_Insurances.iSequence_Number=(select max(iSequence_Number) FROM Trans_Insurances X WHERE X.cTrans_fk = Trans_Insurances.cTrans_fk );
>	 INNER JOIN Insurances on ;
>	 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
>	 INNER JOIN Carrier_Branches on ;
>	 Insurances.cCarrier_Branches_fk = ;
>          Carrier_Branches.cCarrier_Branches_pk ;
> 	 INNER JOIN Carriers on ;
>	 Carrier_Branches.cCarriers_fk = Carriers.cCarriers_pk
>
>

>
>you can use ( a lot slower because it grouped Trans_Insurances fully )
>

> INNER JOIN Trans_Insurances on ;
>	 Trans.cTrans_pk = Trans_Insurances.cTrans_fk ;
> INNER JOIN (SELECT cTrans_fk,max(iSequence_Number) iSequence_Number FROM Trans_Insurances GROUP BY 1) Last;
>         ON Trans_Insurances.cTrans_fk = Last.cTrans_fk AND Trans_Insurances.iSequence_Number = Last.iSequence_Number ;
>	 INNER JOIN Insurances on ;
>	 Trans_Insurances.cInsurances_fk = Insurances.cInsurances_pk ;
>	 INNER JOIN Carrier_Branches on ;
>	 Insurances.cCarrier_Branches_fk = ;
>          Carrier_Branches.cCarrier_Branches_pk ;
> 	 INNER JOIN Carriers on ;
>	 Carrier_Branches.cCarriers_fk = Carriers.cCarriers_pk
>
>

>
>Because it is an INNER JOIn, another way put the filter in the WHERE.
>

Are you sure the first would not work? The second doesn't seem to be a way to go, since the inner select tries to group the whole file...