title: Nesting MAX() function inside IIF()?
body: <p>I have nested <code>IIF()</code> functions in my select statement, and inside of each a nested <code>MAX()</code>.  It seems to want me to include this select statement in the GROUP BY.  Here is my query:</p>
  
  <pre><code>SELECT 
  d2s_loader_performance_tbl.employee_id, 
  IIF(d2s_loader_performance_tbl.wk_ending_dt=max(d2s_loader_performance_tbl.wk_ending_dt),"Last Wk",
    IIF(d2s_loader_performance_tbl.wk_ending_dt=max(d2s_loader_performance_tbl.wk_ending_dt)-7,"2 Wks Ago",
      IIF(d2s_loader_performance_tbl.wk_ending_dt=max(d2s_loader_performance_tbl.wk_ending_dt)-14,"3 Wks Ago",
        IIF(d2s_loader_performance_tbl.wk_ending_dt=max(d2s_loader_performance_tbl.wk_ending_dt)-21,"4 Wks Ago")))),
  d2s_loader_performance_tbl.hrs_worked, 
  d2s_loader_performance_tbl.move_count, 
  d2s_loader_performance_tbl.moves_per_hour
  
  FROM d2s_loader_performance_tbl
  WHERE d2s_loader_performance_tbl.wk_ending_dt In (
  SELECT DISTINCT TOP 4 wk_ending_dt 
  FROM d2s_loader_performance_tbl
  ORDER BY wk_ending_dt DESC)
  
  GROUP BY 
  d2s_loader_performance_tbl.employee_id,
  d2s_loader_performance_tbl.move_count, 
  d2s_loader_performance_tbl.moves_per_hour
  </code></pre>
  
  <p>When I run, I get this error: "You tried to execute a query that does not include the specified expression [lists the chunk that is the nested IIFs] as part of an aggregate function.  When I put this in the GROUP BY (which doesn't make sense), I get this error: "Cannot have aggregate function in GROUP BY clause"</p>
  
  <p>So what in the H-E-double hockey sticks does access want me to do?  The point of this is turning week-ending dates into a relative term.  This report gets updated weekly, and I am tired of manually going through all my queries and reports and changing the references to new dates.</p>
  
  <p>Thank you!</p>