title: MS Access - You tried to execute a query that does not include the specified ... as part of an aggregate function
body: <p>My aggregate function is giving me this error in Microsoft Access 2010. This is the query in question:</p>
  
  <pre><code>SELECT
      curr.FileDate, curr.ProdNr, 
      Count(curr.ProdNr) as CountOfProdNr, 
      curr.Desc, 
      IIf(prev.ProdNr is Null, IIf(curr.Status="1", "NEW", "ERR"), 
      IIf(curr.Status&lt;&gt;prev.Status, 
      Switch(
      curr.Status="7", "CAN", 
      curr.Status="T", "TRM", 
      True, "OTH"), 
      IIf(curr.Status="1", "REG", "HIS"))) as Transaction, 
      curr.Status, 
      prev.Status as PrevStatus, 
      min(curr.StartDate) as MinOfStartDate, 
      max(curr.EndDate) as MaxOfEndDate, 
      curr.CancelCode, 
      max(curr.OrigAmt) as MaxOfOrigAmt,
      Switch(curr.Desc="ML", IIf(MaxOfOrigAmt&gt;500000, 500000, MaxOfOrigAmt), 
      curr.Desc="MC", IIf(MaxOfOrigAmt&gt;300000, 300000, MaxOfOrigAmt), 
      True, MaxOfOrigAmt) as Cap, 
      Switch(prev.Desc="ML", IIf(max(prev.OrigAmt)&gt;500000, 500000, max(prev.OrigAmt)), 
      prev.Desc="MC", IIf(max(prev.OrigAmt)&gt;300000, 300000, max(prev.OrigAmt)), 
      True, max(prev.OrigAmt)) as PrevCap, 
      Switch(curr.Desc="ML", IIf(max(curr.Balance)&gt;500000, 500000, max(curr.Balance)), 
      curr.Desc="MC", IIf(max(curr.Balance)&gt;300000, 300000, max(curr.Balance)), 
      True, max(curr.Balance)) as BalCap, 
      Switch(prev.Desc="ML", IIf(max(prev.Balance)&gt;500000, 500000, max(prev.Balance)), 
      prev.Desc="MC", IIf(max(prev.Balance)&gt;300000, 300000, max(prev.Balance)), 
      prev.Desc Is Null, 0, 
      True, max(prev.Balance)) as PrevBalCap, 
      CDbl(BalCap-PrevBalCap) as BalCapChange, 
      curr.JointIndicator, curr.OrigAmt, 
      max(curr.Balance)-Nz(max(prev.Balance), 0) as BalanceChange
  FROM 
      Cov as curr 
      LEFT JOIN Cov_PREVMONTH as prev 
      ON curr.Ref = prev.Ref
  GROUP BY
      curr.FileDate, 
      curr.Desc, 
      curr.ProdNr, 
      curr.Status, 
      prev.Status, 
      IIf(prev.ProdNr is Null, IIf(curr.Status="1", "NEW", "ERR"), 
      IIf(curr.Status&lt;&gt;prev.Status, 
      Switch(
      curr.Status="7", "CAN", 
      curr.Status="T", "TRM", 
      True, "OTH"), 
      IIf(curr.Status="1", "REG", "HIS"))),
      curr.CancelCode, 
      curr.JointIndicator, 
      curr.OrigAmt
  </code></pre>
  
  <p>Pardon my terrible coding. The error comes for this expression:</p>
  
  <pre><code>Switch(prev.Desc="ML", IIf(max(prev.OrigAmt)&gt;500000, 500000, max(prev.OrigAmt)), 
  prev.Desc="MC", IIf(max(prev.OrigAmt)&gt;300000, 300000, max(prev.OrigAmt)), 
  True, max(prev.OrigAmt))
  </code></pre>
  
  <p>When I try to get around the error and put this expression under the <code>GROUP BY</code> clause, I get the error </p>
  
  <blockquote>
    <p>Cannot have aggregate function in GROUP BY clause</p>
  </blockquote>
  
  <p>I am out of ideas - what's wrong here? </p>
  
  <p>Thank you.</p>