Access交叉表报错：无法识别'MonthEnds.product_id'为有效字段

问题背景 #

原查询可正常计算各产品指定日期前的累计交付量，转换为交叉表（按产品名称作为列展示）时触发错误：

The Microsoft Access database engine does not recognize 'MonthEnds.product_id' as a valid field name or expression

核心原因 #

Access的TRANSFORM子句中嵌套的子查询无法直接引用外层查询的别名（如MonthEnds.product_id），作用域限制导致引擎无法识别该字段。

解决方法 #

先通过预查询计算出每个产品、每个月末的累计交付量，再基于这个预查询构建交叉表，避免在TRANSFORM中嵌套依赖外层字段的子查询。

修改后的交叉表代码 #

TRANSFORM Sum(PreCalc.Qty) AS TotalQty
SELECT 
    PreCalc.D AS MonthEnd,
    PreCalc.D - PreCalc.delay AS Order_By
FROM (
    SELECT
        MonthEnds.product_id,
        T_Products.name,
        MonthEnds.D,
        T_Products.delay,
        (
            SELECT SUM(M.product_qty)
            FROM T_StockMoves M
            WHERE M.product_id = MonthEnds.product_id
              AND M.delivery_date <= MonthEnds.D
              AND M.state_priority >= InputFilter
        ) + NZ(
            (
                SELECT SUM(S.qty)
                FROM T_SalesLines S
                WHERE S.product_id = MonthEnds.product_id
                  AND S.Delivery_date <= MonthEnds.D
                  AND S.State_priority >= Inputfilter
            ), 0
        ) AS Qty
    FROM (
        SELECT
            AllDates.product_id,
            DATESERIAL(YEAR(DateAdd("m", 1, AllDates.D)), MONTH(DateAdd("m", 1, AllDates.D)), 1) - 1 AS D
        FROM (
            SELECT StockMoves.product_id, StockMoves.Delivery_date AS D
            FROM T_StockMoves StockMoves
            UNION
            SELECT SalesLines.product_id, SalesLines.Delivery_date
            FROM T_SalesLines SalesLines
        ) AS AllDates
    ) AS MonthEnds
    INNER JOIN T_Products ON MonthEnds.product_id = T_Products.id
) AS PreCalc
GROUP BY PreCalc.D, PreCalc.D - PreCalc.delay
PIVOT PreCalc.name;

代码说明 #

1. 预查询PreCalc：完整保留原非交叉表的计算逻辑，提前算出每个product_id、每个月末日期D对应的累计Qty，同时关联产品名称和延迟天数。
2. 外层交叉表：基于预查询结果，直接使用Sum(PreCalc.Qty)作为TRANSFORM的聚合项，彻底避免嵌套子查询对外层字段的依赖，Access引擎可正常识别所有字段。

原代码对比 #

报错的交叉表代码 #

TRANSFORM 
    Sum(
        (
            SELECT
                    SUM(M.product_qty)
            FROM
                    T_StockMoves M
            WHERE
                    M.product_id     = MonthEnds.product_id
            AND     M.delivery_date  <= MonthEnds.D
            AND     M.state_priority >= InputFilter
        )+Nz(
            (
                SELECT
                        SUM(S.qty)
                FROM
                        T_SalesLines S
                WHERE
                        S.product_id     = MonthEnds.product_id
                AND     S.Delivery_date  <= MonthEnds.D
                AND     S.State_priority >= Inputfilter
            ),0
        )
    ) AS Qty
SELECT
        MonthEnds.D                  AS MonthEnd,
        MonthEnds.D-T_Products.delay AS Order_By
FROM
        (
            SELECT
                    AllDates.product_id,
                    DATESERIAL(YEAR(DateAdd("m", 1, AllDates.D)), MONTH(DateAdd("m", 1, AllDates.D)), 1) - 1 AS D
            FROM
                    (
                            SELECT
                                    StockMoves.product_id,
                                    StockMoves.Delivery_date AS D
                            FROM
                                    T_StockMoves StockMoves
                            
                            UNION
                            
                            SELECT
                                    SalesLines.product_id,
                                    SalesLines.Delivery_date
                            FROM
                                    T_SalesLines SalesLines
                    ) AS AllDates
        ) AS MonthEnds
INNER JOIN
        T_Products
ON
        MonthEnds.product_id = T_Products.id
GROUP BY
        MonthEnds.D,
        MonthEnds.D-T_Products.delay PIVOT T_Products.name;

可正常运行的非交叉表代码 #

SELECT
        MonthEnds.product_id   ,
        T_Products.name        ,
        MonthEnds.D                  AS MonthEnd,
        MonthEnds.D-T_Products.delay AS Order_By,
        (
                SELECT
                        SUM(M.product_qty)
                FROM
                        T_StockMoves M
                WHERE
                        M.product_id     = MonthEnds.product_id
                AND     M.delivery_date  <= MonthEnds.D
                AND     M.state_priority >= InputFilter
        ) + NZ(
            (
                SELECT
                        SUM(S.qty)
                FROM
                        T_SalesLines S
                WHERE
                        S.product_id     = MonthEnds.product_id
                AND     S.Delivery_date  <= MonthEnds.D
                AND     S.State_priority >= Inputfilter
            ),0
        ) AS Qty
FROM
        (
                SELECT
                        AllDates.product_id,
                        DATESERIAL(YEAR(DateAdd("m", 1, AllDates.D)), MONTH(DateAdd("m", 1, AllDates.D)), 1) - 1 AS D
                FROM
                        (
                                SELECT
                                        StockMoves.product_id,
                                        StockMoves.Delivery_date AS D
                                FROM
                                        T_StockMoves StockMoves
                                
                                UNION
                                
                                SELECT
                                        SalesLines.product_id,
                                        SalesLines.Delivery_date
                                FROM
                                        T_SalesLines SalesLines
                        ) AS AllDates
        ) AS MonthEnds
INNER JOIN
        T_Products
ON
        MonthEnds.product_id = T_Products.id;

内容的提问来源于stack exchange，提问作者Spencer Barnes