CODEWITHSUNDEEP
sqlms-accessjoinalias
A. Reynolds
A. Reynolds

Reputation: 65

MS Access treats my column/alias as a parameter... But only sometimes

New to SQL but I'm fairly sure that my query is correct. I've tried it a thousand different ways but it will still sometimes treat my column/alias lead_ID_t as a parameter. I've put square brackets around everything to try to help but to no avail...

I'm editing the query in notepad++, and when I paste it into Access, it runs just fine. Then I save and close the query. When I try to run it again, it treats lead_ID_t as a parameter and my formatting is completely gone... Can anyone explain what is happening? 

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. same error occurs with and without the iif statement

Upvotes: 1

Views: 274

Answers (1)

Parfait
Parfait

Reputation: 107567

Consider an adjusted, compact query:

  1. Remove redundant nested levels as you can use WHERE in an aggregate GROUP BY query.

  2. Remove DISTINCT as aggregate queries already return distinct grouping values.

  3. Replace IIF() (statement that returns zero for NULL) for NZ().

  4. While Total may not be a listed MS Access or JET reserved word, it might be a GUI application reserved word and thereby causing issues. Use previous subquery aliases, TData and FData.

  5. Alternatively, use conditional aggregation for one main query without subqueries or joins and where you can use calculated columns to avoid repetition.

Compact query

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];

Conditional aggregation

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]

Upvotes: 1

Related Questions