Anyone know how to do this?

[orange]

Ajax,
Thanks for the info.

I modified some of the Trandate values. I then saw the difficulty with the ID values not following the <=.


My attempt then was to redesign EagleBill and create a new table EagleBill2.
For EagleBill I changed the autonumber to Number and renamed the field from Id to IDOLD. For EagleBill2, I added a new field ID. I populated EagleBill2 using this query

INSERT INTO EagleBill2 ( IDOld, trandate, credit, debit )
SELECT IDOld, trandate, credit, debit
FROM EagleBill
ORDER BY EagleBill.tranDate;

The above query added the autonumber ID based on ascending TranDate.

The original query was adjusted:

SELECT eaglebill2.id, eaglebill2.TranDate, eaglebill2.Credit, eaglebill2.Debit
, Format(DSum("Nz(Credit,0) - NZ(Debit,0)","eaglebill2","ID<=" & ID),"Currency") AS RunningBalance
FROM eaglebill2
ORDER BY ID;

The routine to check execution was modified

Sub ToProcessEagleBillRecords()
    Debug.Print "Processing " & CurrentDb.TableDefs("Eaglebill2").RecordCount & "  records from EagleBill2"
    Dim timer As clsTimer
    Set timer = New clsTimer
    timer.StartTimer
    DoCmd.OpenQuery "RunningTot_EagleBill"
    Debug.Print "Completed the query in " & timer.EndTimer & "  ~millisecs" & vbCrLf & Now
    Set timer = Nothing
End Sub

The result summary is:

Processing 7918 records from EagleBill2
Completed the query in 36 ~millisecs
24/11/2015 7:53:05 PM

Here are some records of the output:

id
TranDate
Credit
Debit
RunningBalance

2
15/10/2015
$100.00


$200.00

3
17/10/2015


$100.00
$100.00

4
24/10/2015
$100.00


$200.00

5
24/10/2015
$100.00


$300.00

6
02/11/2015
$200.00


$500.00

7
02/11/2015
$100.00


$600.00

8
03/11/2015


$100.00
$500.00

9
04/11/2015
$300.00


$800.00

10
06/11/2015
$400.00


$1,200.00

11
06/11/2015


$300.00
$900.00

12
07/11/2015


$200.00
$700.00

13
08/11/2015
$200.00


$900.00

14
22/11/2015
$100.00


$1,000.00

15
22/11/2015
$100.00


$1,100.00

16
22/11/2015


$100.00
$1,000.00

17
23/11/2015


$100.00
$900.00

18
23/11/2015
$100.00


$1,000.00

19
23/11/2015
$100.00


$1,100.00

20
23/11/2015
$100.00


$1,200.00

21
24/11/2015
$100.00


$1,300.00

22
24/11/2015


$100.00
$1,200.00

23
24/11/2015
$100.00


$1,300.00

24
24/11/2015
$100.00


$1,400.00

25
24/11/2015
$100.00


$1,500.00

I'm not sure that I fully understand the underlying issue. My thoughts are to get the ID ascending and to account for differing dates. I hope it's useful.

[CJ_London]

I'm not sure that I fully understand the underlying issue.

Issue is to show the balance in date order - like a bank statement - but needs order within order because you can have many transactions on the same day. OP originally said the entries were entered in date order so just working off the ID field would work. You solution is similar to mine - I created a unique order id by combining date with the original ID which can be stored in the table at the time of record creation.

[orange]

Perhaps the OP could supply some data.
I could rebuild some to include time of day.
However, I think my logic handles the situation.

Consider a number of transactions.
Each has an ID and a Date with Time
Each has an amount either debit or credit.
The ID values may not be incremental/sequential.

The approach:
Create a temp table, with a new ID( autonumber field or DMax (intfld) +1). Fill it using a query on the original table, sort the data on TranDate (which includes time). The new ID will be sequential.

Use the query I posted in #31. It should be fine since all records are sequenced. You should get a RunningBalance.

[GraeagleBill]

A few additional elements have evolved as the app begins to take shape, namely the transaction type TTypeID that dictates the order of all transactions for any given date. With "TDate" being the primary key, the sort order is "TDate, TTypeID". I've tried to extract some of the ideas from previously posted suggestions in constructing a sub-query that includes the sort order of the sub-query, but so far I've not come up with anything that doesn't produce anything other than format errors. Although I've not done so yet, I think I should Index the TTypeID field in table tblRegister, that table formerly named tblChkReg.

BTW, Allen Browne includes in his query tips that the use of the NZ function results in a time costly VBA call whereas SUM(IIF(Credit Is Null,0,Credit) - IIF(Debit Is Null,0,Debit)) is native to JET/ACE. If I can figure out what's required in the sub-query worthy enough to post here in consideration of answers I shall do so.

Happy Thanksgiving,
Bill

[GraeagleBill]

Finally! While it is not good practice to store calculated values in a table, I found in this particular app that the value of "Balance" is subject to re-calculation on a frequent basis and so it is now included in the tblRegister table. AND, to do the calculations in a sub-query in this case renders the query uneditable.

The "solution" then is to issue the Requery followed immediately by a call to a very fast module that re-calculates the balance values. 3,000 records faster than one can blink.

Here's the final query for the Register:

SELECT tblRegister.TDate, tblRegister.TTypeID, IIf([tblRegister.TTypeID]>50,[tblRegister.TTypeID],[tblTTypes.TType]) AS TranType, tblRegister.Description, tblRegister.Memo, tblAccounts.AcctName, tblCategories.CategoryName, tblSubCategories.SubCategoryName, tblRegister.Debit, tblRegister.Credit, tblRegister.Balance
FROM (((tblRegister LEFT JOIN tblTTypes ON tblRegister.TTypeID = tblTTypes.TTypeID) LEFT JOIN tblAccounts ON tblRegister.AcctID = tblAccounts.AcctID) LEFT JOIN tblCategories ON tblRegister.CatID = tblCategories.CatID) LEFT JOIN tblSubCategories ON tblRegister.SubCatID = tblSubCategories.SubCatID
ORDER BY tblRegister.TDate, tblRegister.TTypeID;

And from Design View:




"TDate" is the Primary key and it is "TTypeID" (Type of transaction) that controls where any transaction added to the table ends up in the date sequence after a Requery.

I hope all those that followed this thread were able to find some value in return for their time.

Thanks to all and Happy Thanksgiving,
Bill