在转换/数据透视/交叉表查询中不存在的地方使用

Question

日安。正如主题所示，我有两个结构相同的表，名为names_allpawns和names_withforeclosure。但是，我已经有了一个没有WHERE语句的查询的工作版本，因为我还需要在names_allpawns中获取不在names_withforeclosure中的名称，这导致了下面的尝试：

TRANSFORM 
  IIF(
    Sum([ nap.pwn_vol ]) IS NULL, 
    0, 
    Sum([ nap.pwn_vol ])
  ) AS Total 
SELECT 
  nap.pwn_cluster AS Cluster, 
  nap.pwn_area AS Area, 
  nap.pwn_fullname AS Fullname 
FROM 
  names_allpawns AS nap
WHERE 
  NOT EXISTS (
    SELECT 
      1 
    FROM 
      names_withforeclosure as nwf 
    WHERE 
      nwf.pwn_fullname = names_allpawns.pwn_fullname
  )
GROUP BY 
  nap.pwn_cluster, 
  nap.pwn_area, 
  nap.pwn_fullname PIVOT nap.pwn_date;

但是，当我试图运行/保存"MS Access Database不承认'names_allpawns.pwn_fullname‘为有效的字段名或表达式“的查询时，会出现一个错误。这是令人困惑的，因为WHERE语句在非枢轴/转换查询中工作得很好。

编辑：感谢M. Akbar Zain和June7，我通过使用以下查询完成了我想做的事情：

此查询充当查找汇总查询，下面的第二个查询将其用作基本查询：

SELECT 
  names_allpawns.pwn_cluster, 
  names_allpawns.pwn_area, 
  names_allpawns.pwn_fullname, 
  names_allpawns.pwn_vol, 
  names_allpawns.pwn_val, 
  names_allpawns.pwn_date 
FROM 
  names_allpawns 
  LEFT JOIN names_withforeclosure ON (
    names_allpawns.pwn_cluster = names_withforeclosure.pwn_cluster
  ) 
  AND (
    names_allpawns.pwn_area = names_withforeclosure.pwn_area
  ) 
  AND (
    names_allpawns.[pwn_fullname] = names_withforeclosure.[pwn_fullname]
  ) 
WHERE 
  (
    (
      (
        names_withforeclosure.pwn_fullname
      ) Is Null
    )
  );

TRANSFORM 
  IIf(Sum([pwn_vol]) Is Null,0,Sum([pwn_vol])) AS Total
SELECT
  q_get_records_noforeclosure.pwn_cluster,
  q_get_records_noforeclosure.pwn_area,
  q_get_records_noforeclosure.pwn_fullname
FROM
  q_get_records_noforeclosure
GROUP BY
  q_get_records_noforeclosure.pwn_cluster, 
  q_get_records_noforeclosure.pwn_area, 
  q_get_records_noforeclosure.pwn_fullname
PIVOT 
  q_get_records_noforeclosure.pwn_date;

Answer 1

这一行是一个问题：

nwf.pwn_fullname = names_allpawns.pwn_fullname

您在子查询中嵌套了此names_allpawns.pwn_fullname，而access不识别该子查询。

尝试使用联接，首先捕获完整的数据，然后将它们放在TRANSFORM查询中。