What is the correct syntax of RODBC query in R
<p>I'm trying to query an Access database using RODBC in R using the code below:</p>

<pre><code>library(RODBC)
channel &lt;- odbcConnectAccess("S:\\mypath\\PhysiPopDONOTTOUCH\\Physiology.mdb")

data &lt;- sqlQuery( channel , paste ("select Endoscopy.*,
Histology.Diagnosis from Endoscopy JOIN PatientData ON
Endoscopy.HospNum_Id=PatientData.HospNum_Id  JOIN Histology ON
Histology.HospNum_Id=PatientData.HospNum_Id WHERE histology.VisitDate
= Endoscopy.VisitDate"))
</code></pre>

<p>. This is not successful as I get the error:</p>

<pre><code>[1] "42000 -3506 [Microsoft][ODBC Microsoft Access Driver] Syntax
error in FROM clause."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'select Endoscopy.*,
Histology.Diagnosis from Endoscopy JOIN PatientData ON
Endoscopy.HospNum_Id=PatientData.HospNum_Id  JOIN Histology ON
Histology.HospNum_Id=PatientData.HospNum_Id WHERE Histology.VisitDate
= Endoscopy.VisitDate'"
</code></pre>

<p>The tables <code>Endoscopy</code> and <code>Histology</code> are each one to many with the <code>PatientData</code> table. What am I doing wrong with the syntax?</p>