>>>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.
>>
>>Yeah it should be embrassing:) Even in VFP you don't need to concatanate and use an IN query. Join us:) Since that query is unlikely to work in access (syntax wise) here is a version:
>>

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

>>
>>

>>SELECT Doctors.*, Visits.*
>>  FROM Doctors
>>  INNER JOIN
>>  (SubQ INNER JOIN Visits ON
>>  (SubQ.Visit_Date = Visits.Visit_Date) AND (SubQ.Patient_Name = Visits.Patient_Name))
>>  ON Doctors.Doctor_Name = Visits.Doctor_Name;
>>

>>
>>Or with a single SQL:
>>

>>SELECT Doctors.*, Visits.*
>>  FROM Doctors
>>  INNER JOIN
>>  ( SELECT VISITS.patient_name, VISITS.visit_date
>>    FROM VISITS
>>    GROUP BY VISITS.patient_name, VISITS.visit_date
>>    HAVING (((count(VISITS.visit_date))>1)) ) as SubQ
>>  INNER JOIN Visits ON
>>  (SubQ.Visit_Date = Visits.Visit_Date) AND (SubQ.Patient_Name = Visits.Patient_Name))
>>  ON Doctors.Doctor_Name = Visits.Doctor_Name;
>>

>>Cetin
>
>Cetin,
>
>Are you sure this query would work in Access? We're going to test later on (I don't have it installed on my own work computer), but I think yesterday Borislav tested that subquery is not supported as a part of a JOIN. Only VFP9 supports it, in VFP8 you would use the concatenated field. Also I'm not sure, which version is faster: the subquery as where condition or subquery as a JOIN condition.

I don't know why Borislav said subqueries wouldn't work in Access. That syntax is explicitly Access syntax (at least Access 2000 and up). "Only VFP9" is not the one supporting such queries. In fact VFP was late in supporting it. Backends supporting ANSI SQL92 all should support it (Access and SQL server do at least).
For speed you might test both versions.
Cetin