Using WHERE NOT EXIST in a Transform/Pivot/CrossTab query-CodePudding

Good day. As the subject suggests, I have two tables with identical structure named names_allpawns and names_withforeclosure. I already have a working version of this query without WHERE statement however, as I also need to get the names in names_allpawns which are not in names_withforeclosure which leads to this attempt below:

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;

However, I get an error when I try to run/save the query where "MS Access Database does not recognize 'names_allpawns.pwn_fullname' as valid field name or expression." which is confusing since that WHERE statement works fine in a non pivot/transform query.

EDIT: Thanks to M. Akbar Zain and June7, I have accomplished what I was trying to do by making using the following queries:

This query acts as the FIND UMATCHED query which is used by the second query below as base:

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;

CodePudding user response：

This line is an issue:

nwf.pwn_fullname = names_allpawns.pwn_fullname

you have nested this names_allpawns.pwn_fullname inside sub-query, which access does not recognize.

try to use joins instead, first catch full data, then put them inside TRANSFORM query.