This seems to return part of the information I want:

SELECT Patient_Name, count(*) as Num_Specialties
FROM (SELECT Patient_Name,  Doctor_Specialty FROM DOCTORS 
INNER JOIN VISITS ON DOCTORS.doctor_name = VISITS.doctor_name
group by Patient_Name, Doctor_Specialty order by 1,2) temp 
GROUP BY Patient_Name;

How can I add number of doctors to this query?


>Hi everybody,
>
>I have the following tables in Access database:
>
>Patients
>Visits
>Doctors
>
>Here is the assignment:
>
>For each patient in VISITS, give the patient's name, the number of doctors that the patient went to, and the number of different specialties.
>
>
>I'm trying this query:
>

>SELECT vis.Patient_Name, count(vis.Doctor_Name) AS Number_Of_Doctors,
>(select count(Doctor_Specialty) from Doctors
>inner join Visits on Doctors.Doctor_Name = Visits.Doctor_Name
>where Visits.Patient_Name = Vis.Patient_Name
>group by Visits.Patient_Name) AS Number_Of_Specialities
>FROM VISITS AS Vis
>GROUP BY vis.Patient_Name;
>

>
>Which doesn't seem to be correct. E.g. I have a patient who went to 3 doctors, two of them have the same specialty. I'm expecting to see
>

>Name       #Doctors    #Specialties
>Patient1   3           2

>
>but instead I see
>
>
>Name 3 3
>
>What is wrong here?
>
>Thanks in advance.