Better approach than DSUM for running subtotals on query field

[thardyjackson]

I have a calculated field [pmtSumAfterPmt] that’s a running total of payments for each customer. It's based on DSUM. It is super slow -- as a point of reference, adding that one field to the query causes an Excel export of 1500 results to take 6 minutes instead of 3 seconds; viewing the results in the table is very sluggish. I'm wondering if there's a better approach or maybe I've botched something up.

Here's the basic structure:
* tbl_customer (300 records) --- basic information on customers (name, account #, etc.)
* tbl_payments (1500 records) -- information downloaded from our bank (payment amount, payment date, customer account # manually typed by customer, etc.) - it also has a [nbrCustAcctOverride] field I can later use if customer mistyped account number or I want to ignore the transaction.
* qry_pmtTblProcessing -- a cleaned up version of tbl_payments which categorizes transactions, removes spaces from account numbers, applies any overrides, etc.
* qry_pmtRcptAnalysis -- joins tbl_customer and qry_pmtTblProcessing so I can compare whether customer is on track with their payments. An important field is [pmtSumAfterPmt] which is the running total of payments for each customer. I need to know this value after each payment. It's the only "D" function in these 2 queries.

FYI -- the reason I join on a qry instead of a table is that I need to process the info in the tbl_payments table before doing the join.

A snippet of qry_pmtRcptAnalysis is below.

[FONT=Courier New]SELECT qry_pmtTblProcessing.*, tbl_customers.*,[/FONT]
[FONT=Courier New]CDbl(DSum("[pmtCompletedInOut]","qry_pmtTblProcessing","CDbl([pmtDate]) <= " & CDbl([pmtDate]) & " And [nbrCustAcctUsed]='" & [nbrCustAcctUsed] & "'")) AS pmtSumAfterPmt,[/FONT]

[FONT=Courier New][ NOTE: A few other calculations omitted ][/FONT]

[FONT=Courier New]FROM qry_pmtTblProcessing LEFT JOIN tbl_customers ON qry_pmtTblProcessing.nbrCustAcctUsed=tbl_customers.nbrCustAcct;[/FONT]

 

[thardyjackson]

I just experimented by dumping all data from qry_pmtTblProcessing into a new table and ran qry_pmtRcptAnalysis off of the new table. Exporting qry_pmtRcptAnalysis only took 20 seconds instead of 6 minutes. Was qry_pmtTblProcessing re-running every time DSUM looked up something in that query?

In any case, I'm still looking for a solution if anyone has any ideas. I doubt dumping
qry_pmtTblProcessing into an intermediate table is the right solution for a faster query.

Thanks.

 

[Pat Hartman]

The DSum() runs a completely independent query for each row in "itself". I'm sure the query engine got all twisted up in its shorts over that. I'm surprised it finished in 6 minutes. The engine must be smart enough to limit this type of recursion and bail if it gets too deep.

Domain functions should be avoided in queries at all costs since even when they are not recursive as this one is, they run a separate query for each row of the query's recordset.

You can use a non-equi-join to get better performance. Create an equi-join using the QBE and then switch to SQL view to change the = to <=. You won't be able to go back to QBE once you make that change.

 

[bengiomanni]

You can use a non-equi-join to get better performance. Create an equi-join using the QBE and then switch to SQL view to change the = to <=. You won't be able to go back to QBE once you make that change.

I am having the same problem with this query. Could you show me what you mean? I have a single query based on a linked table from an SQL server. I am pulling several fields, and then creating a calculated field with a running balance [FiscalBalance]. The query by itself actually comes up quickly, but if I try to do anything else beyond this it takes forever. I want to find the date at which the running balance equals $1000 for each fiscal year, but with the >=1000 in the query below it takes forever.

SELECT dbo_FC_FundLedgerUnion_View.AccountID, dbo_FC_FundLedgerUnion_View.FiscalYear, dbo_FC_FundLedgerUnion_View.GiftReceivedDt, dbo_FC_FundLedgerUnion_View.Total, (DSum("Total","dbo_FC_FundLedgerUnion_View","[FiscalYear] = " & [FiscalYear] & " AND [FundLedgerUID] >= " & [FundLedgerUID] & " AND [AccountID] = " & [AccountID] & ""))*1 AS FiscalBalance
FROM dbo_FC_FundLedgerUnion_View
WHERE ((((DSum("Total","dbo_FC_FundLedgerUnion_View","[FiscalYear] = " & [FiscalYear] & " AND [FundLedgerUID] >= " & [FundLedgerUID] & " AND [AccountID] = " & [AccountID] & ""))*1)>=1000))
ORDER BY dbo_FC_FundLedgerUnion_View.AccountID, dbo_FC_FundLedgerUnion_View.FiscalYear, dbo_FC_FundLedgerUnion_View.GiftReceivedDt;

 

[Pat Hartman]

You are still using the DSum(). You need to get rid of the DSum() and use the join as I suggested.

 

[bengiomanni]

Right, but I don't know how to rework it with your suggestion.

 

[thardyjackson]

Pat --

Tried your suggestion but not getting expected result. Expected result ... if a customer made 3 payments of $10, [pmtSumAfterPmt] (the customer-specific, running total field) should show $10, $20, and $30 next to the relevant payment. Actual result... [pmtSumAfterPmt] shows $10 each time (same as payment amount). I'm sure I'm missing something. Here's the experimental code I tried. BTW, [pmtCompletedInOut] is the value of the customer's payment.

SELECT qry_pmtTblProcessing1.ID, qry_pmtTblProcessing1.pmtDate, tbl_customers.nbrCustAcct, 

[COLOR=Red]Sum([pmtCompletedInOut]) AS pmtSumAfterPmt[/COLOR]

FROM qry_pmtTblProcessing1 LEFT JOIN tbl_customers ON qry_pmtTblProcessing1.nbrCustAcctUsed=tbl_customers.nbrCustAcct
WHERE [COLOR=Red]pmtDate <= pmtDate[/COLOR]
GROUP BY qry_pmtTblProcessing1.ID, qry_pmtTblProcessing1.pmtDate, tbl_customers.nbrCustAcct;

 

[Pat Hartman]

Try:

FROM qry_pmtTblProcessing1 Inner JOIN tbl_customers ON qry_pmtTblProcessing1.nbrCustAcctUsed=tbl_customers.nbrCustAcct
WHERE qry_pmtTblProcessing1.pmtDate <= tbl_customers.pmtDate

Before you spend any more time on this - why are you trying to calculate the running sum in a query? If this is going to be used on a report, the simple, EFFICIENT method is to use the running sum property of the control.

Queries cannot efficiently calculate running sums because row order is a moving target. That's you have to do recursive, non-equi joins (joining a recordset to itself to find all the rows less than the current row)

 

[thardyjackson]

Pat -- Thanks for the response. The running sum is used in SMS messages sent to customers acknowledging their payment (e.g., "Thanks for your payment of $10, you have paid $30 in total. You are only $25 away from saving enough money to get your water pump."). There are 25 SMS message templates depending on the customer's situation (past due, on track, etc.). I run a query that generates the message, export it to an Excel file and then upload that file to a company that sends the messages on our behalf. I have the Access steps automated through a series of control buttons. It needs to be a running total rather than the current payment total because the query might have multiple payments from 1 person. Each acknowledgment needs to show the relevant balance as of that payment.

I don't think your code (as-is) will work for my situation because:
* [pmtDate] is only in qry_pmtTblProcessing1 not tbl_customers.
* I am doing an outerjoin that shows all records in qry_pmtTblProcessing1 because some payments we receive don't belong to customers of this service (long story) -- I want to send those customers a different message ("Who are you and why are you paying us?").

Any other ideas?

Thanks for your support. It's been helpful.

 

[Pat Hartman]

A running sum is different from a current total. To do what you are asking, create a totals query that sums the data. Then create a query that joins to the totals query to get the current balance. No DSum()s or non-equi-self joins required.

 

[thardyjackson]

I think I need a running sum not a current total because the SMS messages are sent in batch. For example, someone sends $10 once at 9AM, once at 10AM and once at 11AM. The next day we download the transaction file from the bank and create the SMS messages. The customer should get 3 messages:
1 - "Thanks for your payment of $10, you have paid $10 in total."
2 - "Thanks for your payment of $10, you have paid $20 in total."
3 - "Thanks for your payment of $10, you have paid $30 in total."

I think your recommendation would produce 3 messages "Thanks for your payment of $10, you have paid $30 in total." Or, did I misunderstand something?

Thanks.

 

[Pat Hartman]

Why would you not summarize the payments and send one message that you have paid 3 payments totaling $30?

 

[thardyjackson]

We want customers to receive 1 SMS for each payment they make so they know it was received and credited to their account. Running balance is preferred over current balance since it shows their balance changing with each payment. Thanks.

 

[Pat Hartman]

I'm not sure how you will get the correct balance for each of the transactions. You will need a unique identifier beyond date since you are trying to accommodate multiple transactions on the same day. You

Select t1.CustID, t1.TranDate, t1.TranAmt, Sum(t2.TranAmt) as RunningSuml
Inner Join t2 On t1.CustID = t2.CustID and t1.TranID >= t2.TranID
Group By t1.CustID, t1.TranDate, t1.TranAmt;

 

[thardyjackson]

I got something working that only takes 10 seconds (big improvement over 6 minutes with DSUM). See below. The biggest challenge is that accurate account numbers don't exist in tbl_payments2 (customer typos, etc.). So, I have to override some account numbers before calculating the running total. My join ON statement includes IIF statement that chooses an override account number (if present). The query would be nearly instantaneous if there were no typos and I didn't have to use the IIF statement.

SELECT A.ID, Sum(B.pmtPaidIn) AS SumOfpmtPaidIn
FROM tbl_payments2 AS A LEFT JOIN tbl_payments2 AS B 
ON (A.pmtDate>=B.pmtDate) AND ( [COLOR=Red]IIf(Len(A.nbrCustAcctOverride)>0,A.nbrCustAcctOverride,A.nbrCustAcctTentative) =  IIf(Len(B.nbrCustAcctOverride)>0,B.nbrCustAcctOverride,B.nbrCustAcctTentative))
[/COLOR]GROUP BY A.ID, A.nbrCustAcctTentative, A.nbrCustAcctOverride, A.pmtPaidIn;

I also tried this code to avoid the IIF statement but speed is much worse. Note that qry_pmtTblProcessing has [nbrCustAcctUsed] which is based on [nbrCustAcctTentative] and [nbrCustAcctOverride].

SELECT A.ID, Sum(B.pmtPaidIn) AS SumOfpmtPaidIn
FROM [COLOR=Red]qry_pmtTblProcessing[/COLOR] AS A LEFT JOIN [COLOR=Red]qry_pmtTblProcessing[/COLOR] AS B 
ON (A.pmtDate>=B.pmtDate) AND [COLOR=Red](A.nbrCustAcctUsed = B.nbrCustAcctUsed )[/COLOR]
GROUP BY A.ID, A.nbrCustAcctTentative, A.nbrCustAcctOverride, A.nbrCustAcctUsed, A.pmtPaidIn;

 

[Pat Hartman]

DSum(), and other domain functions, are useful for one-off situations. I sometimes use them on forms since the user is only looking at one record at a time, you don't notice the extra time caused by the domain function. Just understand that when you use a domain function in a query or code loop, that function is running a completely separate query for every single record or iteration of the loop. In your case it was 1500 queries. There was another poster this week who was processing 90,000 rows and running 1-3 queries for each record inside a code loop. It was taking over 7 hours.