Access 2007-向查询的左联接返回#Error而不是Null [英] Access 2007 - Left Join to a query returns #Error instead of Null

问题描述

类似于此问题，但从未完全解决他们的查询问题:

Similar to this question, but their problem with the query was never fully solved:

#错误显示在多个LEFT JOIN语句中访问当值应为NULL时访问查询

当我希望在进行左连接时看到Null时出现#Error，而该连接的右侧没有相应的记录:

I get #Error when I'm expecting to see a Null when doing a left join where there is no corresponding record on the right hand side of the join:

Chain               CasesPerMonthPerStore   MonthOfFirstOrder
Naturally           2.3                     5/1/2011
Tom's Market        #Error
Livingstons         #Error
EverClear           3.1                     7/1/2012
Bob's Market        2.66                    5/1/2012
Andy's Exports      #Error
Jamestowns          0.89                    7/1/2012

如果我将数据复制到一个表中并保留该表的联接，那会很好，所以我认为查询的语法有问题:

It works fine if I copy the data into a table and left join to this table, so I assume there is something wrong in the syntax of the query:

SELECT 
    MonthRange.Chain,
    MonthRange.CasesShipped/IIf(MonthsSinceFirstOrder.Months>DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1,
                                DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1,
                                MonthsSinceFirstOrder.Months)/NumStores.NumberOfStores AS CasesPerMonthPerStore,
    MonthsSinceFirstOrder.MonthOfFirstOrder
FROM 
    QueryDates, 
    (
        MonthRange 
        INNER JOIN 
        NumStores 
            ON MonthRange.Chain=NumStores.Chain
    ) 
    INNER JOIN 
    MonthsSinceFirstOrder 
        ON MonthRange.Chain=MonthsSinceFirstOrder.Chain;

此SQL返回正确的结果，只是左联接返回#Errors时的行为.

This SQL returns the correct results, it's just its behaviour when Left Joining to it that is returning #Errors.

nb中间出现一个奇怪的Iif语句，以检查自从第一笔订单以来的月数是否大于指定日期范围内包含的月数-因此，如果日期范围为6个月并且第一笔订单为结束日期前9个月使用6；如果第一笔订单仅在结束日期前4个月，则使用4.

nb the strange Iif statement in the middle checks to see if the number of months since the first order is greater than the number of months included in the specified date range - so if the date range has 6 months and the first order was 9 months before the end date, it uses 6; if the first order was only 4 months before the end date, it uses 4.

-编辑更新-

正确，我将查询的元素一一取出，这是我仍然可以最简单地获得左连接错误的方法:

Right, I took elements of the query out one by one, and this is the simplest I can get whilst still recreating the left joining error:

SELECT 
    MonthRange.Chain, 
    DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1 AS CasesPerMonthPerStore
FROM 
    QueryDates, 
    MonthRange;

这就是我加入的方式:

SELECT 
    Chains.Chain, 
    ErrorQuery.CasesPerMonthPerStore
FROM 
    Chains 
    LEFT JOIN 
    ErrorQuery 
        ON Chains.Chain=ErrorQuery.Chain;

此SQL中的任何内容看起来不正确吗?

Does anything in this SQL look wrong?

推荐答案

当查询应基于联接类型返回Null时，如Allen Browne在其文章

While the query should return Null based on the join type, as Allen Browne states in his article, Bug: Outer join expressions retrieved wrongly,

相反，它的行为就像[JET查询优化器]在从较低级别查询返回结果之后正在评估表达式."

"Instead, it behaves as if [the JET query optimizer] is evaluating the expression after it has returned the results from the lower-level query."

因此，如果连接的右侧与左侧不匹配，则必须使用将计算为Null的表达式来选择计算字段.

Consequently, you must select the calculated field using an expression that will evaluate to Null if the right-side of the join doesn't match the left-side.

以您的简化代码为例:

SELECT 
Month.Chain,
DateDiff("m",QueryDates.StartDate,QueryDates.EndDate)+1 AS CasesPerMonthPerStore
FROM
QueryDates,
MonthRange;

SELECT
Chains.Chain,
IIf(IsNull(ErrorQuery.Chain),Null,ErrorQuery.CasesPerMonthPerStore)
FROM
Chains
LEFT JOIN
ErrorQuery
ON Chains.Chain=ErrorQuery.Chain;

这篇关于Access 2007-向查询的左联接返回#Error而不是Null的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！