>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.

SELECT Visits.Patient_Name
, count(Visits.Doctor_Name) AS Number_Of_Doctors, 
, count(DISTINCT Doctors.Doctor_Specialty)  AS Number_Of_Specialities
FROM Doctors 
inner join Visits on Doctors.Doctor_Name = Visits.Doctor_Name
GROUP BY Visits.Patient_Name;

but i guess you want this:

SELECT Visits.Patient_Name
, count(DISTINCT Visits.Doctor_Name) AS Number_Of_Doctors, 
, count(DISTINCT Doctors.Doctor_Specialty)  AS Number_Of_Specialities
FROM Doctors 
inner join Visits on Doctors.Doctor_Name = Visits.Doctor_Name
GROUP BY Visits.Patient_Name;