title: Aggregate query in MS-Access that calculates current and cumulative amounts by account is exceptionally slow
body: <p>I'm having trouble with an Access query. Although it runs , it is <strong><em>exceptionally</em></strong> slow; and I fear I may be overlooking a simpler, more elegant solution in my query design.  </p>
  
  <p>For context, I work in an accounts receivable office. We have thousands of customers, and each customer can have one or more accounts.  Every month, transactions post to the various accounts, and I am preparing the invoices for the customers. In my particular case, a customer's first invoice is always 001, then 002, and so on. We bill monthly.</p>
  
  <p>To describe a <em>simplified</em> example, in month of January 2020, customer A may have the following transactions in the Transaction table:</p>
  
  <pre>
  +-----------------------------+
  |TransID|Account|Amount|InvNum|
  +-----------------------------+
  |1      |1      |$10.00|001   |
  |2      |2      |$5.00 |001   |
  |3      |3      |$2.00 |001   |
  +-----------------------------+
  </pre>
  
  <p>So, in the above example, I would want to issue invoice 001 to customer A for a total of $17.00, broken out by account.  The invoice would look something like this:</p>
  
  <pre>
  
       +-----------------------+
       |Account|Current|ToDate |
       |1      |$10.00 |$10.00 |
       |2      |$5.00  |$5.00  |
       |3      |$2.00  |$2.00  |
       +-----------------------+
                $17.00  $17.00
  
  </pre>
  
  <p>Now, suppose that in February 2020, additional transactions post. A simplified version of the Transaction table would look like this:</p>
  
  <pre>
  +-----------------------------+
  |TransID|Account|Amount|InvNum|
  +-----------------------------+
  |1      |1      |$10.00|001   |
  |2      |2      |$5.00 |001   |
  |3      |3      |$2.00 |001   |
  |4      |1      |$3.00 |002   |
  |5      |3      |$4.00 |002   |
  +-----------------------------+
  </pre>
  
  <p>Invoice #002 issued to customer A would need to look something like this:</p>
  
  <pre>
       +-----------------------+
       |Account|Current|ToDate |
       |1      |$3.00  |$13.00 |
       |2      |$0.00  |$5.00  |
       |3      |$4.00  |$6.00  |
       +-----------------------+
                $7.00   $24.00
  </pre>
  
  <p>The query I'm having trouble with is specifically designed to capture the month's activity by account <strong>and</strong> to calculate a cumulative total for the "ToDate" column on the invoice.  The challenge is that not every account will have transactions in a given month.  Note that account 2 did not post any transactions in February.  So invoice 002 has to show a current amount of $0.00 for account 2, but it also needs to know the cumulative amount ($5.00 + $0.00 = $5.00) for account 2.</p>
  
  <p>The problematic query is itself made up of a few subqueries:</p>
  
  <ol>
  <li><strong><em>BillNumByAcccountQ</em></strong>: an aggregate query that selects and groups all accounts by invoice number. </li>
  <li><strong><em>CurrentQ</em></strong>: Also an aggregate query that selects and sums all the transaction amounts (from the transaction table), which is left-joined to BillNumByAccountQ. The left-join is necessary to ensure that there is a row for every bill number. The "Current" field in this query is given by the expression <strong><em>Sum(Nz(Amount,0))</em></strong>. The result set of this query contains over 20K rows.</li>
  </ol>
  
  <p>Finally, the problematic query is defined by the following SQL statement:</p>
  
  <pre><code>SELECT 
     Q1.Account
      ,Q1.InvNum
      ,Q1.CURRENT
      ,(
          SELECT SUM(CURRENT)
          FROM CurrentQ
          WHERE Q1.Account = Account
              AND Q1.InvNum &gt;= InvNum
          ) AS ToDate
  FROM CurrentQ AS Q1;
  </code></pre>
  
  <p>This query runs and runs and runs, and it eventually causes Access to stop responding. I do not even know how many rows it has because it never finishes running.  I fear that I'm overlooking a way simpler solution.</p>
  
  <p>Apologies for so much information, and I appreciate any advice on simplifying this.</p>