Anyone know how to do this?

[GraeagleBill]

Anyone know how to write query that produces a running total where records are of the form:



T0 (T0 taken from first of n records.)

__________D1----C1-----T1 T1 = D1 + C1 + T0

__________D2----C2-----T2 T2 = D2 + C2 + T1

__________D3----C3-----T3 T3 = D2 + C2 + T2

etc

etc

Consider a DB table of several thousand records and a change is made to a Dx or Cx field. A simple Requery would then ripple the effected Tx ControlSource of a form's text box.

While this could be done with a simple RecordsetClone in code, it would be far more desirable to have the calculations done within the query.

[GraeagleBill]

This might be a bit clearer:

Table fields
__________D1----C1-----T1 (T1 = D1 + C1 + T0)

__________D2----C2-----T2 (T2 = D2 + C2 + T1)

__________D3----C3-----T3 (T3 = D2 + C2 + T2)


Where Tx is calculated in the query. (And, for example, made to be the ControlSource of a form's non-editable textbox.)

[GraeagleBill]

Should have previewed the last post, as the formatting went away.

[Micron]

I think I get the first two of the sequence. After that I don't get why T3 is not equal to D3+C3, so no clue how to accomplish the result you show 'cause i don't get the pattern/sequence.
P.S. if by formatting you mean position, you can add a table to your post using the menu bar in the composition window or grab from Excel & dump here.

[GraeagleBill]

D and C are fields in a table. T is to be a calculated ControlSource of a text box in a continuous form. T needs to be a running total by adding D and C of the current record to the calculated value of T from the previous record.

Below is a snippet screenshot of what it would look like in an app like Quicken:

[Micron]

That's what I thought at first, but I don't think that's what you posted. This may be one of those times that a temp table would be the easiest. You could append the Payments and Deposits then do a calculated update on the Balance field. Then flush the table when the form closes.
I do presume you are not storing the balance in a table anywhere that is permanent.

[GraeagleBill]

You presume correctly. That would be a "no no" in my book. The code to ripple the D's and C's is simple enough in calculating the balance T. While I've not done so in the past, I think your idea of a temp table would likely be the most suitable approach.
Thanks,
Bill

[CJ_London]

You would normally achieve this with a subquery however your screenshot of quicken may be the way the data is presented, but it is almost certainly not how it is stored.

Also to achieve what you want you need some sort of order based on a unique value (often date, but not sufficient in this case because you could have two records with the same date) - even date and transaction type will be insufficient since you could that the same transaction twice in the same day. This is one of the reasons why a uniqueID for each record is so important.

the basic principle for the query would be something like

SELECT DR, CR, (SELECT sum(CR-DR) FROM myTable AS T WHERE UniqueID<=myTable.UniqueID AND TranDate<=myTable.TranDate) AS Balance
FROM myTable
ORDER BY TranDate, UniqueID

[GraeagleBill]

I'll work on this later today, but for sure 99% of tables I create have auto-number keyed ID's. With that, chronology in this instance is insured.

[GraeagleBill]

I see how
(SELECT sum(CR-DR) FROM myTable AS T

yields the algebraic amount to be added, but not how your sample would add that amount to the previous balance.

[CJ_London]

Because it is summing all previous values - suggest try it and see

[GraeagleBill]

I'll give it a go in the AM..............................long day.

[GraeagleBill]

TranID is the table's key autonumber. With the table ordered on TranID, everything is in chronological order according date of entry.

Query as shown below produces an empty "Balance" column. (I'm not entirely confident that I mapped your example correctly.)

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

[GraeagleBill]

Below is an exact mapping of my table and field names into your example. You are right in that sorting on the date is key in that auto-numbers only reflect record addition chronology. E.g., someone enters a record for July 1st when the table is already reflecting activity for clear up to December 16th.

Balance field is empty when the query below is run. Not sure what's going on here.

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

[GraeagleBill]

I'm trying to syntactically decipher the role of "T" as I view your example and my implementation of it. When I run the query, there's no "T" field being displayed, yet I get the sense that it should?
What am I missing?