>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

What error is it giving? Should be related to your data field names.
(((count(VISITS.visit_date))>1))
(((count(*))>1))

Update a missing parentheses before inner select. This works with Northwind.mdb:

SELECT Customers.*,Orders.*
  FROM Customers 
  INNER JOIN 
  ( (SELECT orders.shipvia, orders.shippeddate
    FROM orders 
    GROUP BY orders.shipvia, orders.shippeddate
    HAVING (((count(orders.shippeddate))=5)) )  as SubQ 
  INNER JOIN Orders ON 
  (SubQ.ShippedDate = orders.shippeddate) AND (SubQ.ShipVia = orders.ShipVia))
  ON Customers.customerID=orders.customerID;

Cetin