You need two Select statements

The first gets the last visit:

SELECT patient.PatientID (I hope you have a primary key?!?),;
       name, max(dt) as lastvisit;
   FROM patient,visit;
   WHERE patient.patientid=visit.patientid and year(dt)=1999;
   GROUP BY 1;
   INTO CURSOR lastvisits

The second gets the billamt for that visits:

SELECT lastvisits.patientID, name, lastvisit, billamt;
   FROM lastvisits JOIN Visits;
       ON lastvisits.patientid=visits.patientID AND;
          lastvisits.lastvisit=vistis.dt;
   GROUP BY 1;
   INTO ...

HTH


>Hi every VFP users, I need to do an inexact SQL match between 2 or more tables but couldn't get it to work the way I want it. I have a patients table and a medical visits table. I need to find the last visit record of each patient in the year 1999. My SQL goes somthing like this.
>
>SELECT name, max(dt), billamt from patient,visit where year(dt)<=1999
>
>I could get the right date but the bill amount does not retrieve correctly from the last visit record for year 1999.
>
>Best regards