repeated records

[Apprentice101]

hello everyone,

I have two tables, one is patients table the other one is followup patients.

I have a request to select all the patients and their latest followup.
When I run my query I have tons of records of the same patient since there is one record per patient on the patient table and many followups. Can someone please tell me if there is any SQL code or access tool to just select my all my patients and their latest follow up only?

Thanks so much!

 

[amorous]

Sure...Try something like this:

SELECT t1.*
FROM Patients t1
WHERE t1.Followupdate In (select top 1 t2.Followupdate from Patients t2 where t2.PatientId = t1.PatientId ORDER BY t2.Followupdate Desc);


-VJ

 

[flyover789]

do you have a datefield in your followup table?
if so you could use Max(followup.thedate)
which would retrieve only the latest follow up.

The same could be used with an autoincremented id field in the followup table.

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com