>>>>try this simple command, before:
>>>>

>>>>SELECT patient_name,count(DISTINCT Doctor_Name) as num from Visits
>>>>

>>>>
>>>The same missing operator error. I'm using Access 2000. Looks like count(distinct ... ) is not allowed in Access database.
>>
>>If i remember good,
>>the Access IDE it is very limited;
>>a lot of queries that don't work, perfectly work if you send to the Jet Engine via ODBC.
>>
>>try this way :
>>

>>SELECT V.Patient_Name,V.Visit_Date,V.Doctor_Name,D.Doctor_Specialty
>>FROM
>>(SELECT Visits.Patient_Name,Visits.Visit_Date
>>  FROM Doctors inner join Visits on Doctors.Doctor_Name = Visits.Doctor_Name
>>	GROUP BY Visits.Patient_Name,Visits.Visit_Date
>>	HAVING MIN(Visits.Doctor_Name)<>MAX(Visits.Doctor_Name)
>>            AND MIN(Doctors.Doctor_Specialty)=MAX(Doctors.Doctor_Specialty)) X
>>  INNER JOIN  Visits V ON X.Patient_Name=V.Patient_Name AND X.Visit_Date=V.Visit_Date
>>  INNER JOIN  Doctors D ON D.Doctor_Name = V.Doctor_Name
>>
>>SELECT V.Patient_Name,V.Visit_Date,V.Doctor_Name,D.Doctor_Specialty
>>FROM
>>(SELECT Visits.Patient_Name,Visits.Visit_Date
>>  FROM Doctors inner join Visits on Doctors.Doctor_Name = Visits.Doctor_Name
>>	GROUP BY Visits.Patient_Name,Visits.Visit_Date
>>	HAVING MIN(Visits.Doctor_Name)<>MAX(Visits.Doctor_Name)
>>            AND MIN(Doctors.Doctor_Specialty)<>MAX(Doctors.Doctor_Specialty)) X
>>  INNER JOIN  Visits V ON X.Patient_Name=V.Patient_Name AND X.Visit_Date=V.Visit_Date
>>  INNER JOIN  Doctors D ON D.Doctor_Name = V.Doctor_Name
>>

>>
>>I see a bad hour for you :)
>
>Syntax error (missing operator).

SELECT X.Patient_Name,X.Visit_Date,V.Doctor_Name,X.Min_Doctor_Specialty
FROM
(SELECT Visits.Patient_Name,Visits.Visit_Date
,       MIN(Visits.Doctor_Name) AS Min_Doctor_Name
,       MAX(Visits.Doctor_Name) AS Max_Doctor_Name
,       MIN(Doctors.Doctor_Specialty) AS Min_Doctor_Specialty
,       MAX(Doctors.Doctor_Specialty) AS Max_Doctor_Specialty
  FROM Doctors inner join Visits on Doctors.Doctor_Name = Visits.Doctor_Name
	GROUP BY Visits.Patient_Name,Visits.Visit_Date
	HAVING Min_Doctor_Name<>Max_Doctor_Name
          AND Min_Doctor_Specialty=Max_Doctor_Specialty) X
INNER JOIN  Visits V ON X.Patient_Name=V.Patient_Name AND X.Visit_Date=V.Visit_Date