title: How do I fix the syntax of a sub query with joins?
body: <p>I have the following query:</p>
  
  <pre><code>SELECT tours_atp.NAME_T, today_atp.TOUR, today_atp.ID1, odds_atp.K1, today_atp.ID2, odds_atp.K2
  FROM (players_atp INNER JOIN (players_atp AS players_atp_1 INNER JOIN (today_atp INNER JOIN odds_atp ON (today_atp.TOUR = odds_atp.ID_T_O) AND (today_atp.ID1 = odds_atp.ID1_O) AND (today_atp.ID2 = odds_atp.ID2_O) AND (today_atp.ROUND = odds_atp.ID_R_O)) ON players_atp_1.ID_P = today_atp.ID2) ON players_atp.ID_P = today_atp.ID1) INNER JOIN tours_atp ON today_atp.TOUR = tours_atp.ID_T
  WHERE (((tours_atp.RANK_T) Between 1 And 4) AND ((today_atp.RESULT)="") AND ((players_atp.NAME_P) Not Like "*/*") AND ((players_atp_1.NAME_P) Not Like "*/*") AND ((odds_atp.ID_B_O)=2))
  ORDER BY tours_atp.NAME_T;
  </code></pre>
  
  <p>I'd like to add a field to this query that provides me with the sum of a field in another table (<code>FS</code>) with a few criteria applied.</p>
  
  <p>I've been able to build a stand alone query to get the sum of <code>FS</code> by <code>ID_T</code> as follows:</p>
  
  <pre><code>SELECT tbl_Ts_base_atp.ID_T, Sum(tbl_Ts_mkv_atp.FS) AS SumOfFS
  FROM tbl_Ts_base_atp INNER JOIN tbl_Ts_mkv_atp ON tbl_Ts_base_atp.ID_Ts = tbl_Ts_mkv_atp.ID_Ts
  WHERE (((tbl_Ts_base_atp.DATE_T)&gt;Date()-2000 And (tbl_Ts_base_atp.DATE_T)&lt;Date()))
  GROUP BY tbl_Ts_base_atp.ID_T, tbl_Ts_mkv_atp.ID_Ts;
  </code></pre>
  
  <p>I now want to match up the sum of <code>FS</code> from the second query to the records of the first query by <code>ID_T</code>. I realise I need to do this using a sub query. I'm confident using these when there's only one table but I consistently get 'syntax errors' when there are joins.</p>
  
  <p>I simplified the first query down to remove all the <code>WHERE</code> conditions so it was easier for me to try and error check but no luck. I guess the resulting SQL will also be easier for you guys to follow:</p>
  
  <pre><code>SELECT today_atp.TOUR, (SELECT Sum(tbl_Ts_mkv_atp.FS)
  FROM tbl_Ts_mkv_atp INNER JOIN (tbl_Ts_base_atp INNER JOIN today_atp ON tbl_Ts_base_atp.ID_T = today_atp.TOUR) ON tbl_Ts_mkv_atp.ID_Ts = tbl_Ts_base_atp.ID_Ts AS tt
  WHERE tt.DATE_T&gt;Date()-2000 And tt.DATE_T&lt;Date() AND tt.TOUR=today_atp.TOUR
  ORDER BY tt.DATE_T) AS SumOfFS
  FROM today_atp
  </code></pre>
  
  <p>Can you spot where I'm going wrong? My hunch is that the issue is in the <code>FROM</code> line of the sub query but I'm not sure. Thanks in advance.</p>