No, It didn't work in Access (2003).

>>>Hi everybody,
>>>
>>>My colleague is asking help with Access query. He has Visits table and Doctors table. We need to find information about patients, that have more than one visits with their doctors and doctor's specialty.
>>>
>>>This part works
>>>
>>>

>>>SELECT VISITS.patient_name, VISITS.visit_date
>>>FROM VISITS GROUP BY VISITS.patient_name, VISITS.visit_date
>>>HAVING count(VISITS.visit_date)>1;
>>>

>>>
>>>Now we need to join with the doctors table
>>>
>>>INNER JOIN DOCTORS ON VISITS.doctor_name = DOCTORS.doctor_name
>>>
>>>In VFP I would write something like
>>>
>>>select * from Visits inner join Doctors where patient_name + dtos(date) in (select patient_name + dtos(date) from ... having ...)
>>>
>>>The problem is that I don't remember the Access function that converts date to string and could not find it in Access help.
>>>
>>>Could you bright minds help me with this SQL and save me from the total embarrassment?
>>>
>>>Thanks a lot in advance.
>>
>>

>>select V.* from Visits V
>>JOIN (SELECT patient_name, visit_date
>>      FROM VISITS GROUP BY patient_name, visit_date
>>      HAVING count(visit_date)>1) X
>>   ON V.patient_name=X.patient_name AND V.visit_date=X.visit_date
>>

>
>I am not sure that Access supports SELECT in JOIN clause. I don't have Access on my work computer to test.