Fabio,

In your last reply you seem to be addressing different problem. Here is the problem we're having trouble with:

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.



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