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:

Enterprise DNA: Master Data Skills + AI

Learn data and AI skills from world-class experts to drive impactful change in your career, your business, and your world. Ready to start learning?

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.

 

[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.

 

[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