>>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.
>
>>The SQL becomes very complex...

Hmm, where would be simpler to implement.

and Trans_Insurances.iSequence_Number = ;
(select max(iSequence_Number) from TI ;
group by ... where TI.cTrans_fk = Trans.cTrans_pk)

What do you think?