MS Access运行嵌套SQL时提示输入d.[Open Amount]参数原因咨询

错误原因 #

• 最内层的agg子查询中，你没有给data表设置别名d，却在字段列表中引用了d.[Open Amount]，Access无法识别这个不存在的别名关联字段，因此触发参数输入提示。
• 额外的逻辑冗余问题：agg子查询中不需要查询[Open Amount]字段，该字段后续会从关联的data表（别名d）中读取，此处属于多余引用。

修正后的SQL语句 #

SELECT [doc type], [Open Amount]
     , [customer number]
     , COUNT([customer number]) As CountCustomerNumber
     , SUM(IIF([Open Amount]>'0', [Open Amount], 0)) AS sum_open_amount_pos   
     , SUM(IIF([Open Amount]<'0', [Open Amount], 0)) As sum_open_amount_neg         
FROM 
      (SELECT d.[customer number] & d.[membership number] AS CustMemb
            , d.[customer number]
            , agg.[doc type]
            , d.[Open Amount]
        FROM  (SELECT [doc type]
                    , [customer number]
               FROM   data
               WHERE  [doc type] = 'RU'
              ) agg
        INNER JOIN [data] d                                   
           ON  d.[customer number] = agg.[customer number]
        GROUP  BY d.[customer number] & d.[membership number]
                , d.[customer number]
                , agg.[doc type]
      ) AS sub
GROUP  BY [doc type]
        , [customer number]
        ,[Open Amount]
HAVING COUNT([customer number]) = 1

提示：如果[Open Amount]是数值类型字段，IIF条件里的'0'建议去掉单引号改为0，避免隐式类型转换带来的计算误差。

内容的提问来源于stack exchange，提问作者John Daly