Does Dsum drag form down?

[ClaraBarton]

I have a check register with over 9,000 records. On a check form I keep a running balance (DSum) with a criteria of date and check number and a second field with a DSum showing cleared balance.
The form wants to t-h-i-n-k and t-h-i-n-k when closing and occasionally crashes completely.
Are these Dsum fields the problem or do I look for something else?

 

[arnelgp]

as an alternative, you can use a Running Balance query.
Dlookup its value.

 

[ClaraBarton]

excuse me? how would this look?

 

[The_Doc_Man]

Here are some articles:

Running Total SQL: Cumulative Sum Examples – Master Data Skills + AI

blog.enterprisedna.co

Calculate Running Total in SQL - GeeksforGeeks

Your All-in-One Learning Portal: GeeksforGeeks is a comprehensive educational platform that empowers learners across domains-spanning computer science and programming, school education, upskilling, commerce, software tools, competitive exams, and more.

www.geeksforgeeks.org

You can look up "Running Total" to see more articles. Also, note that there is a "Similar Threads" article that might be helpful.

 

[ClaraBarton]

OK... get your point. Look it up. So I guess the answer is DSum is a drag and not the best choice.

 

[MajP]

Yes. Each dsum is in sense it own query. Therefore you are opening 9,000 queries.

 

[ClaraBarton]

Thank you. Good reply.

 

[Pat Hartman]

The problem will be that your form will not be updateable if it includes functions to keep the running sum. Another option would be to create a query that creates the running sum and bind it to a subform. Add the subform to the subform (you can size it to be the size of a control and remove all the scroll bars and other stuff that makes it look like a form. The master/child links will keep it sync'd with the subform's current record. I haven't actually tried this. The idea just occurred to me but it sounds feasible.

 

[ClaraBarton]

The queries I have tried get seriously slow. I haven't figured how to have a cleared balance and a running balance that's usable.

 

[Pat Hartman]

Have you tried the left-join method. This only works if there is a UNIQUE sequential identifier. Date is insufficient. Date and time might work. You join the table to itself on the unique identifier using a standard join. Change it to a totals query so you can sum the amount. Then switch to SQL View and change the = to <= so that all rows with an identifier <= to the current ID will be summed to show the running balance.

This is the most efficient method that i know of. I'm sure that someone will write the query as a subselect. Jet/ACE do NOT optimize subselects well so unless you absolutely need one (and in this case you don't), juse use the standard left join. With SQL Server, I don't know what you get so you could try both methods.

 

[arnelgp]

The queries I have tried get seriously slow.

so maybe it's not the DSum() that is causing the trouble, it's the way you construct your slow sql.

 

[ClaraBarton]

Ok... Way over my head here

SELECT CheckingT.CheckingID, 
Sum(CheckingT.Debit) AS SumOfDebit, 
Sum(CheckingT.Credit) AS SumOfCredit, 
CheckingT.Cleared, 
CheckingT.[Credit]-CheckingT.[Debit] AS RunBalance
FROM CheckingT 
LEFT JOIN CheckingT AS CheckingT_1 ON CheckingT.CheckingID <= CheckingT_1.CheckingID
GROUP BY CheckingT.CheckingID, CheckingT.Cleared;

Is this close?

 

[June7]

What happens when you open the query in Datasheet?

Don't open it in Design View. IIRC, it can't handle non-equal join.

 

[ClaraBarton]

Doesn't work. But I know it isn't right because I don't get the logic in self joins. Says the fields could come from either table. So I renamed the second table to SecondCheck but which fields do I use there?

 

[ebs17]

with over 9,000 records

The calculation should be done by querying all records?
I ask because past bookings should no longer change (in good accounting) and you could save RunTotals this way.
DCount/DSum as a correlated sub-query is complex, my personal limit for use is 10,000 records, which is about where it becomes practically unfeasible due to slowness.
Contrary to expectations, a subquery using SQL as a correlated subquery is significantly slower and is not an alternative in Jet-SQL.

The query in #13 looks good from an approach. But it's not correct yet, and I can't yet estimate how long it will take.
I'm interested in such tasks.

I still have no idea what exactly you want to calculate. Can you upload the CheckingT table (with around 20 records) and a representation of the desired query table from these 20 records here, preferably as Excel tables?

 

[ClaraBarton]

 

[Gasman]

I think @ebs17 meant the actual file and not a picture?
How is anyone meant to work with that?

 

[ebs17]

The following suggestions should produce the desired result.
Untested and unmeasured because query execution is not possible with an image, @Gasman can read and understand well..

SELECT
   C.CheckingID,
   C.Debit,
   C.Credit,
   C.Cleared,
   Nz(C.Credit, 0) - Nz(C.Debit, 0) + DSum("Nz(Credit, 0) - Nz(Debit, 0)", "CheckingT", "CheckingID <= " & C.CheckingID) AS RunningBalance,
   Nz(C.Credit, 0) - Nz(C.Debit, 0) + DSum("Nz(Credit, 0) - Nz(Debit, 0)", "CheckingT", "Cleared = True AND CheckingID <= " & C.CheckingID) AS ClearedBalance
FROM
   CheckingT AS C

2 * 9000 = 18000 DSum > 10000
Performance is probably catastrophic.

SELECT
   C.CheckingID,
   Nz(C.Debit) AS Debit,
   Nz(C.Credit) AS Credit,
   C.Cleared,
   Nz(C.Credit) - Nz(C.Debit) + SUM(Nz(RB.Credit) - Nz(RB.Debit) AS RunningBalance,
   Nz(C.Credit) - Nz(C.Debit) + SUM(Nz(CB.Credit) - Nz(CB.Debit) AS ClearedBalance
FROM
   (CheckingT AS C
      LEFT JOIN CheckingT AS RB
      ON C.CheckingID >= RB.CheckingID
   )
   LEFT JOIN
      (
         SELECT
            *
         FROM
            CheckingT
         WHERE
            Cleared = True
      ) AS CB
      ON C.CheckingID >= CB.CheckingID
GROUP BY
   C.CheckingID,
   Nz(C.Debit),
   Nz(C.Credit),
   C.Cleared

As you can see, combining debit and credit is a hindrance and increases effort. You can probably do without some of the Nz uses, but to be on the safe side I've stayed more detailed.

The better table design would be if there was only one field for payment; a debit payment could then be identified with a minus sign. This makes mathematical calculations much easier. You could place a composite index on pure table fields for faster grouping, but of course this doesn't work with calculated fields.

If you need the debit and credit fields for an expense, you can easily create them, but only after all the necessary calculations have been made beforehand.

SELECT
   CheckingID,
   Payment,
   IIf(Payment >= 0, Payment, NULL) AS  Credit,
   IIf(Payment < 0, Abs(Payment), NULL) AS Debit
FROM
   QueryOfCheckingT