MS Access将我的列/别名作为参数.但有时

Question

新的SQL，但我相当肯定我的查询是正确的。我已经尝试过上千种不同的方法，但有时它仍然会将我的列/别名lead_ID_t作为一个参数。我把所有的东西都放在方括号里，试图帮忙，但没有用.

我在notepad++中编辑查询，当我将它粘贴到Access中时，它运行得很好。然后保存并关闭查询。当我再次尝试运行它时，它将lead_ID_t作为一个参数，而我的格式已经完全消失了。有人能解释一下发生了什么吗？

SELECT [Total].[lead_ID_t] AS [Lead ID], 

IIF(isnull([Total].[lead_count_t]-[F9].[lead_count_f]),0,[Total].[lead_count_t]-[F9].[lead_count_f]) AS [V Calls]

FROM (SELECT DISTINCT [Lead_ID] AS [lead_ID_t], Count([Lead_ID]) AS [lead_count_t] 

FROM (SELECT * FROM [Logs] WHERE [log_actor] <> "Five9 Dialer") As [TData] GROUP BY [Lead_ID])  AS [Total] 

LEFT JOIN (SELECT DISTINCT [Lead_ID] AS [Lead_ID_f], count([Lead_ID]) AS [lead_count_f] 

FROM (SELECT * FROM [Logs] WHERE [log_actor] = "Five9 Dialer") As [FData] GROUP BY [Lead_ID])  AS [F9] ON [Total].[lead_ID_t]=[F9].[lead_ID_f];

ps。在iif语句和不使用iif语句时都会发生相同的错误。

Answer 1

考虑一个经过调整的、紧凑的查询：

1. 删除冗余嵌套级别，因为您可以在聚合WHERE查询中使用GROUP BY。
2. 删除DISTINCT，因为聚合查询已经返回不同的分组值。
3. 替换IIF() (NULL返回零的语句)用于NZ()。
4. 虽然Total可能不是列出的MS Access或JET保留字，但它可能是GUI应用程序的保留字，从而引起问题。使用以前的子查询别名、TData和FData。
5. 或者，对一个没有子查询或联接的主查询使用条件聚合，并且可以在其中使用计算过的列以避免重复。

紧凑查询

SELECT [TData].[lead_ID_t] AS [Lead ID],
       NZ(([TData].[lead_count_t] - [FData].[lead_count_f]),0) AS [V Calls]
FROM
  (SELECT [Lead_ID] AS [lead_ID_t],
          COUNT([Lead_ID]) AS [lead_count_t]
   FROM [Logs]
   WHERE [log_actor] <> 'Five9 Dialer'
   GROUP BY [Lead_ID]) AS [TData]

LEFT JOIN    
  (SELECT [Lead_ID] AS [Lead_ID_f],
          COUNT([Lead_ID]) AS [lead_count_f]
   FROM [Logs]
   WHERE [log_actor] = 'Five9 Dialer' 
   GROUP BY [Lead_ID]) AS [FData]

ON [TData].[lead_ID_t] = [FData].[lead_ID_f];

条件聚合

SELECT l.[Lead ID],
       SUM(IIF(l.[log_actor] <> 'Five9 Dialer', 1, 0)) AS [lead_count_t],
       SUM(IIF(l.[log_actor] = 'Five9 Dialer', 1, 0)) AS [lead_count_f],
       NZ(([lead_count_t] - [lead_count_f]),0) AS [V Calls]
FROM [Logs] l
GROUP BY l.[Lead_ID]