We finally had a chance to work with my colleague. We do want only patients with the 2 visits on the same date. So, the original inner select was correct.

Here is the query that works:

select V.patient_name, V.visit_date, V.doctor_name, DOCTORS.doctor_specialty

from Visits V INNER JOIN DOCTORS ON V.doctor_name=DOCTORS.doctor_name

WHERE EXISTS(SELECT patient_name, visit_date FROM Visits

                    WHERE patient_name=V.patient_name

                      AND visit_date=V.visit_date

group by patient_name, visit_date

having count(VISITS.visit_date)>1

)

This is giving an error in Access:

SELECT Doctors.*, Visits.*
  FROM Doctors 
  INNER JOIN 
  ( SELECT VISITS.patient_name, VISITS.visit_date
    FROM VISITS 
    GROUP BY VISITS.patient_name, VISITS.visit_date
    HAVING (((count(VISITS.visit_date))>1)) ) as SubQ 
  INNER JOIN Visits ON 
  (SubQ.Visit_Date = Visits.Visit_Date) AND (SubQ.Patient_Name = Visits.Patient_Name))
  ON Doctors.Doctor_Name = Visits.Doctor_Name;

>>I think I set up the condition wrongly. We want all patients that have more than 1 visit in total with all visits information. Right now we're getting patients that have more than 1 visits on the same day.
>
>Yes, your original query was pointing to that. Then just remove the date component from subselect.
>Cetin