MS Access关联Crosstab查询与子查询时引擎无法识别字段如何解决

错误原因 #

这是MS Access查询优化器的已知兼容问题：当查询同时关联交叉表查询和包含相关子查询的查询时，优化器会错误识别子查询的别名作用域，导致无法找到子查询内的字段，和拼写、语法无关。

可行解决方案（无需创建临时表） #

• 方案1：改写带相关子查询的日期查询，用域函数替代子查询，消除别名作用域冲突
  将QryDate的SQL修改为：

  SELECT B2.Batch, 
  DLookup("LastPallet", "TblBatches", "ID < " & B2.ID & " ORDER BY ID DESC") AS PrevEnd
  FROM TblBatches AS B2;
  

  改写后直接运行原汇总查询即可正常执行。

• 方案2：拆分汇总查询的嵌套层级，先完成主表和交叉表的关联，再关联日期查询
  将汇总查询的SQL修改为：

  SELECT Tmp.Batch, Tmp.Availability, Tmp.Performance, Tmp.Quality, Q2.PrevEnd
  FROM 
  (
    SELECT B1.Batch, Q1.Availability, Q1.Performance, Q1.Quality 
    FROM TblBatches AS B1 
    LEFT JOIN QryCrosstab AS Q1 ON B1.Batch = Q1.Batch
  ) AS Tmp
  LEFT JOIN QryDate AS Q2 ON Tmp.Batch = Q2.Batch;
  

  拆分查询作用域后，优化器不会再混淆子查询的别名。

• 方案3：给交叉表查询显式固定输出列，避免Access动态解析交叉表结构
  将QryCrosstab的SQL修改为：

  TRANSFORM Sum(Q1.Downtime) AS SumOfDowntime 
  SELECT Q1.Batch 
  FROM [QryDowntime] Q1
  GROUP BY Q1.Batch 
  PIVOT Q1.Category IN ('Availability', 'Performance', 'Quality');
  

  固定交叉表的输出列后，优化器不需要动态计算查询结构，也可解决冲突。

内容的提问来源于stack exchange，提问作者Matt Schmucki