ms-access - valor null access 2007

Access 2007-Left Unirse a una consulta devuelve#Error en lugar de Null

Question

Similar a esta pregunta, pero su problema con la consulta nunca fue completamente resuelto:

#Error que aparece en la instrucción LEFT JOIN múltiple Consulta de acceso cuando el valor debe ser NULL

Obtengo #Error cuando espero ver un Nulo al hacer una combinación de la izquierda donde no hay registro correspondiente en el lado derecho de la unión:

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

Funciona bien si copio los datos en una tabla y dejo unirme a esta tabla, así que supongo que hay algo mal en la sintaxis de la consulta:

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;

Este SQL devuelve los resultados correctos, es solo su comportamiento cuando Left Joining devuelve #Errors.

nb la extraña instrucción Iif en el centro comprueba si el número de meses desde el primer pedido es mayor que el número de meses incluidos en el intervalo de fechas especificado, por lo que si el intervalo de fechas tiene 6 meses y el primer pedido fue 9 meses antes la fecha de finalización, usa 6; si la primera orden fue solo 4 meses antes de la fecha de finalización, usa 4.

- ACTUALIZAR EDITAR -

Bien, saqué elementos de la consulta uno por uno, y esto es lo más simple que puedo obtener mientras sigo recreando el error de unión izquierdo:

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

Y así es como me queda unirse a él:

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

¿Algo en este SQL se ve mal?

Answer 1

Parece que podría tratarse de un error conocido en Access, por el cual comete errores en uniones externas con campos calculados:

http://allenbrowne.com/BugOuterJoinExpression.html

y

http://allenbrowne.com/bug-10.html

Así que voy a ver si puedo cambiar las subconsultas para disfrazar los campos calculados de alguna manera.

Me encanta Access. :)

Answer 2

Mientras que la consulta debe devolver nulo en función del tipo de combinación, como afirma Allen Browne en su artículo, error: expresiones de unión externa recuperadas incorrectamente ,

"En cambio, se comporta como si [el optimizador de consultas JET] estuviera evaluando la expresión después de haber devuelto los resultados de la consulta de nivel inferior".

En consecuencia, debe seleccionar el campo calculado usando una expresión que evaluará a Nulo si el lado derecho de la unión no coincide con el lado izquierdo.

Usando su código simplificado como ejemplo:

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;