>I have a patient referrals table which identifies patients by field MNO and has a referral date field REFDATE. The table contains multiple records for each patient for each time the patient is referred:
>
>MNO REFDATE
>
>100 98.01.01
>100 98.03.02
>100 98.02.05
>101 98.01.05 (different patient)
>100 98.07.02
>
>Records are NOT necessarily added to the table in date order, but the table is indexed on REFDATE and related to the main patient table by MNO.
>
>Problem: I need a SELECT statement which will retrieve each MNO record and its latest REFDATE, i.e. in the example above it would be
>record 5. This statement is to list one record only per patient with the latest REFDATE only.
>
>Any ideas please?
Bob, try
SELECT DISTINCT mno, refdate from mytable ORDER by refdate DESCENDING INTO CURSOR tmp
Nick
Nick Neklioudov
Universal Thread Consultant
3 times Microsoft MVP - Visual FoxPro
"I have not failed. I've just found 10,000 ways that don't work." - Thomas Edison