Hi!

Name and BillAmt have no sense in such queries with aggreagate functions, because there might be more than one record with max(dt). You require to query these data another way:

SELECT name, dt, billamt from patient,visit where
dt IN (SELECT max(dt) from patient,visit WHERE year(dt)<=1999)

This way you get all records which have max(dt) and correct related fields.

Take a look also to the help in MSDN for SELECT statement. I recommend you to read specially chpters related to JOINS and WHERE. I guess this query is way too slow because you link 2 tables in a full join.

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