I didn''t see the reason of JOIN and with second select. Try this:
select Patient_name, Visit_date, MAX(other_field), COUNT(*) AS Cnt from Visits;
FROM VISITS
GROUP BY VISITS.patient_name, VISITS.visit_date
HAVING Cnt>1
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
