Creating a Running Balance Query

[JamesJoey]

I'm trying to create a query that adds the Credit column and subtracts the Debit column, like a checking registry.
I've created the table.
I got this example from a video on the web.
I created a query with all fields in table plus a 'Balance' field for a calculated field:

So far all I get is access asking for a parameter value.
Any idea why this isn't working?

James

 

[theDBguy]

Try changing Expr1: [Balance]=[Credit]-[Debit] into simply this:

Balance: [Credit]-[Debit]

 

[CJ_London]

DBG is correct, however a Running Balance (per the thread title) is a term usually applied to columns rather that records. If what you are after is something like Running Balance = running balance from previous record plus this record credit less this record debit then you need a different solution

There are several, but which is most appropriate depends on your actual setup

 

[JamesJoey]

That worked fine.
It was difficult for me to read it and just took a guess.

Thank,
James

 

[JamesJoey]

I thought it worked.
It is placing the value of either the Credit or Debit in each row.
It doesn't give me a running total.

 

[CJ_London]

per post #3 provide some more detail - is the resulting dataset required to be editable? you also need some means to determine the order at the record level - a timestamp field or autonumber PK may be sufficient providing data is always entered in the correct order. A date field might be OK providing there is never more than one record created on the same day. Or maybe you need a combination of a date field and a PK. All depends on your data.

 

[JamesJoey]

I'm using a continuous form, and yes I am able to edit it.
I'm trying to create a checking account register.
I didn't realize it was so difficult.

 

[CJ_London]

I didn't realize it was so difficult.

it's not if you have the data in a way that enables it - which you have not provided and I don't have the time to guess what you might have

The fact you are able to edit it doesn't answer the question -- do you want it to be editable? Reports for example have a running sum option, but reports are not editable. But you could put them in a subform control on a form which can add new records and with some work could 'call up' an existing record to be edited. But even with a report, you still need to be able to specify an order.

 

[JamesJoey]

I do want it to be editable.
I was under the impression that one can create formula for an unbound field that displays the balance between the 'Credit' and 'Debit' currency values.

 

[DHookom]

You can use DSum() in your query although it might be very slow and inefficient. Can you provide your field names? I assume you have a date field to use in the WHERE expression of the DSum().

You can try something like:

SELECT TransDate, Credit, Debit, DSum("Credit-Debit","tblChecking","TransDate <=#" & TransDate & "#")

 

[JamesJoey]

'CheckingID' (Auto Number)
'CheckNumber' (Short Text)
'TransDate' (Date/Time)
'Description' (Short Text)
'Status' (Short Text)
'Credit' (Currency)
'Debit' (Currency)

 

[DHookom]

Did you try my suggestion? Are Nulls allowed in Credit and Debit fields? If there are no nulls, try:

SELECT tblChecking.TransDate, tblChecking.Credit, tblChecking.Debit, 
   CCur(DSum("[Credit]-[Debit]","tblChecking","CheckingID<=" & [CheckingID])) AS Balance
FROM tblChecking;

 

[plog]

Credits and Debits should not be separate fields. Combine them into an [Amount] field.

Then the simplest solution is to use negative numbers for credits and positive for debits. Or if you want to classify your transactions (Deposits, Checks, Card, ATM, Interest, etc.) you could add a TransType field then have another table for all transaction types that determine if those transactions are debits (1) or credits (-1).

Whichever you choose, having seperate fields is a mistake.

 

[arnelgp]

see Query1 on the demo.

 

[DHookom]

@arnelgp , the Balance expression can be simplified to :

Balance: Val(DSum("Nz(Credit,0)-Nz(Debit,0)","tblChecking","ID<=" & [ID]))

 

[KitaYama]

see Query1 on the demo.

@arnelgp , the Balance expression can be simplified to :

I think both ways doesn't work.
There's no guarantee that the users input data in the correct sequence.
Imagine a user has forgotten to input a transaction from a previous day and adds it on a later date.
Then if you re-order the query to see the balance, it would look like this:

I would add time to TransDate, use a function to open a recordset ordered by TransDate and do my calculations from there.
Or simply use TransDate as the Criteria of DSum.

 

[arnelgp]

the query is Ordered on:

ORDER BY tblChecking.TransDate, tblChecking.ID;

therefore if there are Same date on the transaction, the order will be then determined by the ID.

//Edit: it is easier to use a function instead.

 

[KitaYama]

therefore if there are Same date on the transaction, the order will be then determined by the ID.

I added a record for 11/10 to your table and this is what your query shows.

 

[DHookom]

The TransDate and some calculation based on the ID like date + ID/10000 in the DSum() WHERE.

 

[KitaYama]

The TransDate and some calculation based on the ID like date + ID/10000 in the DSum() WHERE.

As I said, I defenitely will go for a user defined function. It's more flexible than Where clause of a DSum.
Just a personal preference.