No, it will not work. You can not use GROUP BY and relate the fields, AFAIK.

>Hi Jerry,
>
>Wouldn't this work? (If you do not need more fields from Table2 you can do just ignore the second join and)
>
>

>SELECT		Table1.*, ;
>		A.*, ;
>		Table2.* ;
>	FROM	Table1 ;
>	LEFT	OUTER JOIN	( ;
>		SELECT		MAX(B.effdt) as maxeffdt, ;
>				B.SetID, ;
>				B.DeptID ;
>			FROM	Table2 B ;
>			WHERE	B.effdt < Table1.effd ;
>			GROUP	BY B.SetID, B.DeptID ;
>			) A ON Table1.setid = A.setid AND Table1.deptid = A.deptid ;
>	LEFT	OUTER JOIN Table2 ON Table1.setid = Table2.setid AND Table1.deptid = Table2.deptid AND Table2.effdt = A.maxeffdt
>

>
>>To All,
>>
>>I have a piece of code that runs perfectly in Oracle environment and now want to bring it to VFP,
>>but getting error correlating fields:
>>
>>SELECT Table1.*,;
>> Table2.*;
>> FROM Table1 LEFT OUTER JOIN Table2 A;
>> ON Table1.setid = A.setid AND Table1.deptid = A.deptid;
>> WHERE A.effdt = (SELECT MAX(effdt) FROM Table2 B;
>> WHERE B.setid=A.setid AND B.deptid=A.deptid ;
>> AND B.effdt less then Table1.effdt)
>>
>>
>>I am trying to connect Table 1 to Table2 and select only 1 record from Table2 (effective dated) that is prior to effective date in Table1.
>>
>>Tried to change ENGINEBEHAVIOUR, but the error exist in all.
>>What I am doing wrong?
>>
>>Thanks