>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