title: #Error showing up in multiple LEFT JOIN statement Access query when value should be NULL
body: <p>I'm trying to return an ID's last 4 years of data, if existing. The table (call it A_TABLE) looks like this: ID, Year, Val</p>
  
  <p>The idea behind the query is this: for each ID/Year in the table, LEFT JOIN with Year-1, Year-2, and Year-3 (to get 4 years of data) and then return Val for each year. Here's the SQL:</p>
  
  <pre><code>SELECT a.ID, a.year AS [Year], a.Val AS VAL, 
    a1.year AS [Year-1], a1.Val AS [VAL-1], 
    a2.year AS [Year-2], a2.Val AS [VAL-2], 
    a3.year AS [Year-3], a3.Val AS [VAL-3]
  FROM (
    ([A_TABLE] AS a 
    LEFT JOIN [A_TABLE] AS a1 ON (a.ID = a1.ID) AND (a.year = a1.year+1)) 
    LEFT JOIN [A_TABLE] AS a2 ON (a.ID = a2.ID) AND (a.year = a2.year+2)) 
    LEFT JOIN [A_TABLE] AS a3 ON (a.ID = a3.ID) AND (a.year = a3.year+3)
  </code></pre>
  
  <p>The problem is that, for past years where there is no data (eg, Year-1), I see "#Error" in the appropriate VAL column (eg, [VAL-1]). The weird thing is, I see the expected "null" in the Year column (eg, [YEAR-1]).</p>
  
  <p>Some sample data:</p>
  
  <pre><code>ID     YEAR  VAL
  Dave   2004  1
  Dave   2006  2
  Dave   2007  3
  Dave   2008  5
  Dave   2009  0
  </code></pre>
  
  <p>outputs like this:</p>
  
  <pre><code>ID    YEAR  VAL  YEAR-1  VAL-1  YEAR-2  VAL-2  YEAR-3  VAL-3
  Dave  2004  1            #Error         #Error         #Error
  Dave  2006  2            #Error 2004    1              #Error
  Dave  2007  3    2006    2              #Error 2004    1
  Dave  2008  5    2007    3      2006    2              #Error
  Dave  2009  0    2008    5      2007    3      2006    2
  </code></pre>
  
  <p>Does that make sense? Why am I getting the appropriate NULL val for the non-existent YEARs, but an #Error for the non-existent VALs?</p>
  
  <p>(This is Access 2000. Conditional statements like "IIf(a1.val is null, -999, a1.val)" do not seem to do anything.)</p>
  
  <p>EDIT: It turns out that the errors are somehow caused by the fact that A_TABLE is actually a query. When I put all the data into an actual table and run the same query, everything shows up as it should. Thanks for the help, everyone.</p>