You are right, I can change the select to

select a.Actsid, a.Actssn, a.Actfname, a.Actmname, a.Actlname, p.posname, p.posdate;
from actives a left outer join vPosition vp ON
a.ACTSID = vp.POSSID left outer join Position p ON
vp.possid = p.possid AND p.posdate = vp.posdate ;
where possid = a.actsid ;
order by actlname

You could also add posname to the view, which works with VFP sql but breaks ANSI Sql (all fields must be in the group by except summation fields).


>>One thing you can do is to create a view of the position table
>>
>>CREATE VIEW vPosition AS
>>SELECT position.possid, MAX(position.posdate) as posdate;
>> FROM position;
>> GROUP BY position.posid
>>
>>
>>Then your select will be:
>>
>>select a.Actsid, a.Actssn, a.Actfname, a.Actmname, a.Actlname, p.posname, p.posdate;
>> from actives a left outer join vPosition p ON a.ACTSID = p.POSSID;
>> where possid = a.actsid ;
>> order by actlname
>>
>
>The problem with this is that posname is not a field in vPosition, and you can't add it to the first query - it will not select the posname for the right record.