Anyone know how to do this?

[ssanfu]

From http://www.fmsinc.com/microsoftaccess/vba/nz.htm
"Any arithmetic calculation in VBA with a Null value results in a null value. If one of the values is NULL, the result is Null (blank). According to VBA rules for calculating values, the result is null because a null value is an unknown."

Add the NZ function and the alias "T" (in BLUE):

SELECT tblChkReg.Debit, tblChkReg.Credit, (SELECT sum(NZ(T.Credit,0)-NZ(T.Debit,0)) FROM tblChkReg AS T WHERE T.TranID<=tblChkReg.TranID AND T.TDate<=tblChkReg.TDate) AS Balance
FROM tblChkReg
ORDER BY tblChkReg.TDate, tblChkReg.TranID;

I would also think about removing T.transID from the subquery WHERE clause (In RED)

[GraeagleBill]

I thought earlier about the possible effects of null values but somehow had the idea that there was more to the issue than that possibility and, as I mentioned in my post #15, had put most of my focus on the role of "T". Anyway, I changed the arguments of the SUM function to NZ(T.Credit,0)-NZ(T.Debit,0) and the query produced accurate calculations of the "Balance".

I don't understand your suggestion about "removing T.transID from the subquery WHERE clause (In RED)"? I guess I don't fully understand how the WHERE clause is related to the secondary SELECT as the balance calculated for one record is added to the Debit and Credit of the record that follows.

Thanks,
Bill

[Micron]

subquery syntax requires that you alias certain tables. The effect doesn't show up in the results.
Some good reading on the subject for you:
http://allenbrowne.com/subquery-01.html

[GraeagleBill]

With the query as shown here, and after I've added another record for 8/1/2014 at $100:

SELECT tblChkReg.TranID, tblChkReg.TDate, tblChkReg.Debit, tblChkReg.Credit, (SELECT sum(NZ(T.Credit,0)-NZ(T.Debit,0)) FROM tblChkReg AS T WHERE TranID<=tblChkReg.TranID AND TDate<=tblChkReg.TDate) AS Balance
FROM tblChkReg
ORDER BY tblChkReg.TDate, tblChkReg.TranID;

I get this result. (As you can see, the WHERE condition relating to the TranID has excluded the addition of the $100.)


If I drop the TranID from the WHERE clause I get this:



Doing a bit of manual arithmetic one can see that "bottom line" for a given date aggregate is correct, it's of little value when displaying balance figures in a form.

So, using the primary key TranID (auto-number) isn't going to suffice to establish the proper WHERE condition for the sub-query. I can think of schemes in code to control non-auto record numbers when "out of date sequence" entries are added, but I was hoping someone had a better idea?

Thanks to everyone for your thoughts,
Bill

[ssanfu]

Maybe this?

SELECT tblChkReg.Debit, tblChkReg.Credit, (SELECT sum(NZ(T.Credit,0)-NZ(T.Debit,0)) FROM tblChkReg AS T WHERE T.TDate<=tblChkReg.TDate ORDER BY T.TDate, T.TranID) AS Balance
FROM tblChkReg
ORDER BY tblChkReg.TDate, tblChkReg.TranID;

[GraeagleBill]

I can't figure out what Access is complaining about, as all looks okay to me.

[CJ_London]

compare the code you have posted with the code you are showing in the query window - it should be as you have posted i.e.

.....WHERE TranID<=tblChkReg.TranID AND TDate<=tblChkReg.TDate.....

or if your want a bit understanding

.....WHERE T.TranID<=tblChkReg.TranID AND T.TDate<=tblChkReg.TDate.....

the other possibility is the 'blank' Dr or Cr is null in which case try

....sum(nz(Credit,0)-nz(Debit,0))....

[Micron]

'Scuse if I'm way off base here. Just in case you iron out the syntax and the $100 record is still dropped - I do believe the <= part will create cases where the $100 is dropped because Access stops the comparision if the less than part is satisfied. I might be wrong about that, but to prevent that in the past, I have used, for example, Max(T.Field1) < OtherTable.Field1. I'm offering that because I think you want values from the record which is immediately prior to the last in the comparison, which the <= may not return.
Maybe the ORDER BY suggestion was meant to prevent that...

[CJ_London]

you originally said

tables I create have auto-number keyed ID's. With that, chronology in this instance is insured.

and the code provided was on that basis. It turns out chronology isn't assured so a different solution is required.

You need an additional calculated field to denote the true order - I would suggest this is a combination of the TDate and the TranID to create a combined uniqueID and therefore order

SELECT tblChkReg.TranID, tblChkReg.TDate, tblChkReg.Debit, tblChkReg.Credit, (SELECT sum(NZ(T.Credit,0)-NZ(T.Debit,0)) FROM tblChkReg AS T WHERE (TDate*1000000)+TranID<=(tblChkReg.TDate*1000000)+ tblChkReg.TranID) AS Balance
FROM tblChkReg
ORDER BY (TDate*1000000)+TranID

[GraeagleBill]

Your suggestion works, but very slow even on a very fast desktop machine. About 8 seconds on a table with only 2700 records.

SELECT tblChkReg.TranID, tblChkReg.TDate, tblChkReg.Debit,  tblChkReg.Credit, (SELECT sum(NZ(T.Credit,0)-NZ(T.Debit,0)) FROM  tblChkReg AS T WHERE  (TDate*1000000)+TranID<=(tblChkReg.TDate*1000000)+  tblChkReg.TranID)  AS Balance
FROM tblChkReg
ORDER BY (TDate*1000000)+TranID

[CJ_London]

your tranID should already be indexed, make sure your date field is too.

Only other alternative is to create a field in your table to hold the calculated value and index that as well/instead (don't use a calculated field, they can't be indexed)

Subqueries can be slow on large datasets, no getting around it.

The other thing you can do is to break it up into smaller chunks - in reality how long do you want your report to be? 1 month? A year? so add a critiera to the main query

....
FROM tblChkReg
WHERE TDate >= dateadd("y",-1,Date())
ORDER BY (TDate*1000000)+TranID

[orange]

Bill,

I mocked up some data and created a query. I hope it's helpful.

The data table (EagleBill)

id	tranDate	Credit	Debit
1	02/11/2015	$100.00
2	02/11/2015	$200.00
3	03/11/2015		$100.00
4	04/11/2015	$300.00
5	06/11/2015	$400.00
6	06/11/2015		$300.00
7	07/11/2015		$200.00
8	08/11/2015	$200.00

The query

SELECT eaglebill.id
    ,Credit
    ,Debit
    ,Format(DSum("Nz(Credit,0) - NZ(Debit,0)", "eaglebill", "ID<=" & ID), "Currency") AS RunningBalance
FROM eaglebill

The result:

id	Credit	Debit	RunningBalance
1	$100.00		$100.00
2	$200.00		$300.00
3		$100.00	$200.00
4	$300.00		$500.00
5	$400.00		$900.00
6		$300.00	$600.00
7		$200.00	$400.00
8	$200.00		$600.00

Good luck.

After posting, I realized I didn't include TranDate (I didn't use it but thought I should show it in the output)

revised query

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

Output with the Trandate

id
TranDate
Credit
Debit
RunningBalance

1
02/11/2015
$100.00


$100.00

2
02/11/2015
$200.00


$300.00

3
03/11/2015


$100.00
$200.00

4
04/11/2015
$300.00


$500.00

5
06/11/2015
$400.00


$900.00

6
06/11/2015


$300.00
$600.00

7
07/11/2015


$200.00
$400.00

8
08/11/2015
$200.00


$600.00

[GraeagleBill]

Getting late in California, I'll resume this caper Tuesday afternoon.
Happy Thanksgiving to everyone,
Bill

[orange]

Bill,

I added more records to the table, then wrote a small routine to time the execution of the query.

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

Here is the output:

Processing 7918 records from EagleBill
Completed the query in 25 ~millisecs


Some sample records in the output

id	TranDate	Credit	Debit	RunningBalance
7883	24/11/2015		$100.00	$283,100.00
7884	24/11/2015	$100.00		$283,200.00
7885	24/11/2015	$100.00		$283,300.00
7886	24/11/2015	$100.00		$283,400.00
7887	24/11/2015		$100.00	$283,300.00
7888	24/11/2015		$100.00	$283,200.00
7889	24/11/2015		$100.00	$283,100.00
7890	24/11/2015		$100.00	$283,000.00
7891	24/11/2015	$100.00		$283,100.00
7892	24/11/2015	$100.00		$283,200.00
7893	24/11/2015	$100.00		$283,300.00
7894	24/11/2015	$100.00		$283,400.00
7895	24/11/2015		$100.00	$283,300.00
7896	24/11/2015	$100.00		$283,400.00
7897	24/11/2015	$100.00		$283,500.00
7898	24/11/2015	$100.00		$283,600.00
7899	24/11/2015		$100.00	$283,500.00
7900	24/11/2015	$100.00		$283,600.00
7901	24/11/2015	$100.00		$283,700.00
7902	24/11/2015		$100.00	$283,600.00
7903	24/11/2015	$100.00		$283,700.00
7904	24/11/2015		$100.00	$283,600.00
7905	24/11/2015	$100.00		$283,700.00

[CJ_London]

@Orange

The only problem with your suggestion is that the data is not in ID order and needs to be presented in date order - later ID's can have earlier dates so you will produce the same problem as illustrated in post #19. You need to have an 'ID' value that also represents the correct order