Aggregate query in MS-Access that calculates current and cumulative amounts by account is exceptionally slow

Question

I'm having trouble with an Access query. Although it runs , it is exceptionally slow; and I fear I may be overlooking a simpler, more elegant solution in my query design.

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.

To describe a simplified example, in month of January 2020, customer A may have the following transactions in the Transaction table:

+-----------------------------+
|TransID|Account|Amount|InvNum|
+-----------------------------+
|1      |1      |$10.00|001   |
|2      |2      |$5.00 |001   |
|3      |3      |$2.00 |001   |
+-----------------------------+

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:


     +-----------------------+
     |Account|Current|ToDate |
     |1      |$10.00 |$10.00 |
     |2      |$5.00  |$5.00  |
     |3      |$2.00  |$2.00  |
     +-----------------------+
              $17.00  $17.00

Now, suppose that in February 2020, additional transactions post. A simplified version of the Transaction table would look like this:

+-----------------------------+
|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   |
+-----------------------------+

Invoice #002 issued to customer A would need to look something like this:

     +-----------------------+
     |Account|Current|ToDate |
     |1      |$3.00  |$13.00 |
     |2      |$0.00  |$5.00  |
     |3      |$4.00  |$6.00  |
     +-----------------------+
              $7.00   $24.00

The query I'm having trouble with is specifically designed to capture the month's activity by account and 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.

The problematic query is itself made up of a few subqueries:

BillNumByAcccountQ: an aggregate query that selects and groups all accounts by invoice number.
CurrentQ: 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 Sum(Nz(Amount,0)). The result set of this query contains over 20K rows.

Finally, the problematic query is defined by the following SQL statement:

SELECT 
   Q1.Account
    ,Q1.InvNum
    ,Q1.CURRENT
    ,(
        SELECT SUM(CURRENT)
        FROM CurrentQ
        WHERE Q1.Account = Account
            AND Q1.InvNum >= InvNum
        ) AS ToDate
FROM CurrentQ AS Q1;

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.

Apologies for so much information, and I appreciate any advice on simplifying this.

Please show the underlying SQL for queries: CurrentQ and BillNumByAcccountQ. Try to save CurrentQ as temp table and run final query off this table. — Parfait

Josh Eller Josh Eller · Accepted Answer · 2020-02-25T19:34:55

Generally, doing a sub-query inside a select statement is slow, since it often needs to run the sub-query for every single row of the main query.

Doing the aggregation all at once is likely going to be faster:

SELECT 
     Q1.Account
    ,Q1.InvNum
    ,Q1.CURRENT
    ,SUM(i.CURRENT) AS ToDate
FROM CurrentQ AS Q1
JOIN CurrentQ AS i
     ON  i.Account = Q1.Account
     AND i.InvNum >= Q1.InvNum
GROUP BY Q1.Account, Q1.InvNum, Q1.Current;

In addition, if you're able to edit the database, you'd probably want to add indexes for the Account and InvNum columns.

Aggregate query in MS-Access that calculates current and cumulative amounts by account is exceptionally slow

1 Answers