Running Total In Query Using Debit & Credit Transactions

Question

Running Total in Query: I am attempting to create a query that has running totals using Debits & Credits. I can get this working in a Report but for this example i need a query and am not coming up with the correct formula here. The formula I am using for Running Balance is:
RunningBalance: Nz([Credit],0)-Nz([Debit],0)

ScreenShot of Query

I have tried variations using Dsum and Sum but cannot seem to get any iterations of this formula to work. The formuala I'm using in the example is RunningBalance: Nz([Credit],0)-Nz([Debit],0)

Edit question to post query as formatted SQL code and data as formatted text table, not images. Running totals in Access query is not simple. — June7, Mar 01 '23 at 21:15
Does this answer your question? [Running Total in Access Query](https://stackoverflow.com/questions/44533039/running-total-in-access-query) — June7, Mar 01 '23 at 21:19

score 0 · Answer 1 · answered Mar 03 '23 at 07:36

I worked up a generic answer: Assuming You have a table with columns ID, A,B,C, D like:

----------------------------------------------------------------------------------------------------------
|         ID         |         A          |         B          |         C          |         D          |
----------------------------------------------------------------------------------------------------------
|                  1 | a                  | b                  | c                  |                  1 |
----------------------------------------------------------------------------------------------------------
|                  2 | a                  | b                  | c                  |                  2 |
----------------------------------------------------------------------------------------------------------
|                  3 | a                  | b                  | c                  |                  3 |
----------------------------------------------------------------------------------------------------------
|                  4 | a                  | b                  | c                  |                  4 |
----------------------------------------------------------------------------------------------------------
|                  5 | a                  | b                  | c                  |                  5 |
----------------------------------------------------------------------------------------------------------

Assuming you want a running sum of Column D as ID increases like :

----------------------------------------------------------------------------------------------------------
|         ID         |         A          |         B          |         C          |    RunningTotal    |
----------------------------------------------------------------------------------------------------------
|                  1 | a                  | b                  | c                  |                  1 |
----------------------------------------------------------------------------------------------------------
|                  2 | a                  | b                  | c                  |                  3 |
----------------------------------------------------------------------------------------------------------
|                  3 | a                  | b                  | c                  |                  6 |
----------------------------------------------------------------------------------------------------------
|                  4 | a                  | b                  | c                  |                 10 |
----------------------------------------------------------------------------------------------------------
|                  5 | a                  | b                  | c                  |                 15 |
----------------------------------------------------------------------------------------------------------

Then cut and paste the following sql into the sql pane. (after cutting and pasting Table1 into a text file and then importing table1 into access.)

SELECT Table1.ID, Table1.A, Table1.B, Table1.C, (SELECT SUM(t.D) FROM Table1 as t WHERE Table1.ID>= t.ID) AS RunningTotal
FROM Table1
ORDER BY Table1.ID;

After Saving and switching to the Design Pane you will see something similar to :

When you run the query you will get the desired results.

RunningTotal is calculated using a subquery as a column. In the design view RunningTotal shows as a calculated field. The basic idea is to create a duplicate of the original table (here t) and decide on how you are ordering the RunningTotal( here by ID ascending). From there you can grab the columns from Table1 or t and then calculate RunningTotal from the unused table.

Not really what I'm looking for unfortunately. My data set has debits and credits in separate columns and i need to get a running balance per account based on debit and credit entries. The columns look like: — rebel102285, Mar 03 '23 at 12:20
transaction_id transaction_date account_number debit credit run_bal — rebel102285, Mar 03 '23 at 12:21
The idea is to replace D with a calculated field (Debit + Credit) and if Transaction is a date with time you c\an use it as ID directly or add a TransactionID — mazoula, Mar 04 '23 at 05:35

Running Total In Query Using Debit & Credit Transactions

1 Answers1