title: SQL Syntax Error - Two Column Inner Join
body: <p>I am attempting to get a SQL query working (in Access 2007), but I am repeatedly getting a generic error message about a syntax error, that simply states (not typoed - it seems to truncate the SQL): </p>
  
  <pre><code>Syntax error in query expression 'Metric.ID = Metric_Value_Measurement.Metric_ID AND (SELECT Measurement_Date FROM Metric_Value_Measurement WHERE Metric_ID = '1') = Metric_Value_Measurement.Measurement_Dat'.
  </code></pre>
  
  <p>The SQL I am attempting to use is below - I have checked that the <code>SELECT</code> query that is nested works as expeted outside this query, and if I remove the <code>AND</code> section from the <code>INNER JOIN</code>, the rest of the query works as expected.</p>
  
  <p>Can anyone suggest where my syntax may be wrong?</p>
  
  <pre><code>SELECT Desired_Future_State_Metric.Desired_Future_State_ID
  ,Desired_Future_State_Metric.Metric_ID
  ,Desired_Future_State_Metric.Target_Value
  ,Desired_Future_State_Metric.Target_Date
  ,Metric.Description
  ,Metric.Unit
  ,Metric_Value_Measurement.Measurement_Date
  FROM (
  (
      Metric INNER JOIN Desired_Future_State_Metric 
              ON Metric.ID = Desired_Future_State_Metric.Metric_ID
      ) INNER JOIN Metric_Unitvalue ON Metric.ID = Metric_Unitvalue.Metric_ID
  )
  INNER JOIN Metric_Value_Measurement ON (
      Metric.ID = Metric_Value_Measurement.Metric_ID
      AND (
          SELECT MAX(Measurement_Date)
          FROM Metric_Value_Measurement
          WHERE Metric_ID = Metric.ID
          ) = Metric_Value_Measurement.Measurement_Date
      )
  GROUP BY Desired_Future_State_Metric.Desired_Future_State_ID
  ,Desired_Future_State_Metric.Metric_ID
  ,Desired_Future_State_Metric.Target_Value
  ,Desired_Future_State_Metric.Target_Date
  ,Metric.Description
  ,Metric.Unit
  ,Metric_Value_Measurement.Measurement_Date;
  </code></pre>
  
  <p>EDIT: With thanks to David Wallace below, it appears that the issue is that I am comparing a single value <code>Metric_Value_Measurement.Measurement_Date</code> with multiple values; this was an oversight in adding my code here, as the <code>SELECT</code> statement should have read <code>SELECT MAX(Measurement_Date)</code> to get the most recent date for a given record. Having modified this, I still get a syntax error (same error as before). Is there something else I need to do in order to join these tables?</p>
  
  <p>EDIT 2: As None of the answers so far seem to have resolved the issue, I thought it might be useful to post the structure of the relevant tables:</p>
  
  <pre><code>Metric
  ----------
  ID
  Description
  
  Desired_Future_State_Metric
  ----------
  ID
  Metric_ID
  Desired_Future_State_ID
  Target_Value
  Target_Date
  
  Metric_Value_Measurement
  ----------
  ID
  Metric_ID
  Measurement_Date
  Measurement_Value
  </code></pre>
  
  <p>The goal is to get the most recent <code>Measurement_Value</code> for a given <code>Metric_ID</code> - as the code below doesn't seem to work, I'm open to suggestions as to how I would get this. In summary, the value I'm looking for is the first <code>Measurement_Value</code> for a given <code>Metric_ID</code>, when the list is ordered by date descending.</p>