Hi friends
am just wondering why followin syntax does not work ?? am trying last result date and last result value of each patient.

SELECT
master.NHI as Patient_Numbers_UR_Number,;
master.name as Name_Address_Full_Known_Name,;
(SELECT MAX(rp.resdate) FROM res_pat as rp WHERE ;
rp.master = rp1.master) as Last_result_date,;
(select rp4.value from res_pat as rp4 where rp4.master=rp1.master ;
and rp4.resdate in (SELECT MAX(rp.resdate) FROM res_pat as rp WHERE rp.master = rp1.master)) as [Column4]

from master inner join res_pat as rp1 on master.pkey = rp1.master;
where rp1.pkey in ;
(select res_pat1.pkey FROM master,;
test_pat test_pat1, test test2, res_pat res_pat1;
WHERE ;
(UPPER (PADR(test2.Name,30)) LIKE "BMI");
AND year (res_pat1.Resdate) = 2005 AND;
(master.status = 0 AND test_pat1.MASTER = master.PKEY AND test_pat1.status < 2;
AND test_pat1.TEST = test2.PKEY AND test2.status < 2;
AND res_pat1.TEST_PAT = test_pat1.PKEY AND res_pat1.status < 2);
);
group by 1,2,3,4

it works perfectly in my sql server database but not in foxpro. btw abv command works nicely in foxpro too if i remove "column4".
any ideas ?