>Hi Cetin/Erik
>
>I tried the following:
>
>select patient.sn, patient.fn, patient.dob, referrals.date ;
> from inpatients!patient ;
> left join inpatients!referrals ;
> on patient.mno=referrals.mno ;
> .and. referrals.date IN
> (SELECT TOP 1 referrals.date;
> from referrals;
> where referrals.mno=patient.mno;
> order by referrals.date desc);
> into cursor bob
>
>but get an error message 'Function Name is Missing)'
>
>any ideas....and thanks for your help so far. Incidentally, the most recent is not necessarily the most recent by date as a user may retrospectively add a new record which was missed earlier on; I need the most recent by date
>
>Regards,
>
>Bob
Try this :
select patient.sn, patient.fn, patient.dob, referrals.* ;
from inpatients!patient ;
left join inpatients!referrals ;
on patient.mno=referrals.mno ;
where ;
padl(referrals.mno,10,"0")+dtos(referrals.date) in ;
(select padl(referrals.mno,10,"0")+dtos(max(referrals.date)) ;
from referrals group by referrals.mno) ;
into cursor bob
referrals.* is for showing you don't only access to max(date). Later change it to referrals.date.
Cetin