title: Access report either not showing data or failing with "Multi-level GROUP BY cause not allowed in subquery."
body: <p>I have two queries that I am using to generate a report from, the problem is when I run the report,  three fields do not show any data at all for some reason.</p>
  
  <p>Query 1:</p>
  
  <pre><code>SELECT ClientSummary.Field3 AS PM, 
         ClientSummary.[Client Nickname 2] AS [Project #], 
         ClientSummary.[Client Nickname 1] AS Customer, 
         ClientSummary.[In Reference To] AS [Job Name], 
         ClientSummary.Field10 AS Contract,
  
        (select sum([Billable Slip Value]) 
         from Util_bydate as U1 
         where U1.[Client Nickname 2] = ClientSummary.[Client Nickname 2]) 
            AS [This Week],
  
        (select sum([Billable Slip Value]) 
         from Util as U2 
         where U2.[Client Nickname 2] = ClientSummary.[Client Nickname 2] ) 
            AS [To Date],
  
        [To Date]/[Contract] AS [% Spent],
  
        0 AS Backlog, 
  
        ClientSummary.[Total Slip Fees &amp; Costs] AS Billed, 
        ClientSummary.Payments AS Paid, ClientSummary.[Total A/R] AS Receivable, 
  
        [Forms]![ReportMenu]![StartDate] AS [Start Date], 
        [Forms]![ReportMenu]![EndDate] AS [End Date]
  
  FROM ClientSummary;
  </code></pre>
  
  <p>Query 2:</p>
  
  <pre><code>SELECT JobManagement_Summary.pm, 
         JobManagement_Summary.[project #], 
         JobManagement_Summary.Customer, 
         JobManagement_Summary.[Job Name], 
         JobManagement_Summary.Contract, 
         IIf(IsNull([This Week]),0,[This Week]) AS [N_This Week], 
         IIf(IsNull([To Date]),0,[To Date]) AS [N_To Date], [% Spent], 
         JobManagement_Summary.Backlog, 
         JobManagement_Summary.Billed, 
         JobManagement_Summary.Paid, 
         JobManagement_Summary.Receivable, 
         JobManagement_Summary.[Start Date], 
         JobManagement_Summary.[End Date]
  FROM JobManagement_Summary;
  </code></pre>
  
  <p>When I run the report from query 2 these 3 fields don't appear.  N_This Week, N_To Date and % Spent.   All have no data.   It isn't the IIF functions, as it doesn't matter if I have those in there or remove them.</p>
  
  <p>Any thoughts?   If I connect directly to the first recordset it works fine, but then SQL throws the error message: Multi-level GROUP BY cause not allowed in subquery.</p>
  
  <p>Is there any way to get around that message to link to it directly or does anyone have ANY clue why these fields are coming back blank?   I am at wits end here!</p>