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.
