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.