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

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:

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)

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]).

Some sample data:

ID     YEAR  VAL
Dave   2004  1
Dave   2006  2
Dave   2007  3
Dave   2008  5
Dave   2009  0

outputs like this:

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

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?

(This is Access 2000. Conditional statements like "IIf(a1.val is null, -999, a1.val)" do not seem to do anything.)

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.