#错误应出现在多个LEFT JOIN语句中访问查询，当值应为NULL时 [英] #Error showing up in multiple LEFT JOIN statement Access query when value should be NULL

问题描述

我正在尝试返回ID的最近4年数据(如果有的话).该表(称为A_TABLE)如下所示:ID，Year，Val

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

查询背后的想法是:对于表中的每个ID/年份，使用LEFT JOIN以及Year-1，Year-2和Year-3(以获得4年的数据)，然后返回每年的Val .这是SQL:

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)

问题是，在过去几年中，没有数据(例如Year-1)，我在相应的VAL列(例如[VAL-1])中看到"#Error".奇怪的是，我在年份"列中看到了预期的空"(例如[YEAR-1]).

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

一些示例数据:

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

这样的输出:

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

这有意义吗?为什么我为不存在的YEAR获得适当的NULL值，而为不存在的VAL获取#错误?

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?

(这是Access2000.像"IIf(a1.val为null，-999，a1.val)"之类的条件语句似乎没有任何作用.)

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

事实证明，错误是由A_TABLE实际上是查询这一事实引起的.当我将所有数据放入一个实际的表中并运行相同的查询时，一切都会按原样显示.谢谢大家的帮助.

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.

推荐答案

我认为a_table是查询这一事实可能很重要(请参阅我对您的帖子的评论).

I think that the fact that a_table is a query may matter (see my comment on your post).

这篇关于#错误应出现在多个LEFT JOIN语句中访问查询，当值应为NULL时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！