Problem in calculating 'balance' in ledger. (1 Viewer)

lumiere

New member
Local time
Yesterday, 19:16
Joined
Nov 10, 2019
Messages
21
Hi,
I am making a database to manage my banking transactions, similar to ledger.

The table table would contain 6 fields. Serial no, date, detail, deposit, withdrawal and balance.

I have made a form to enter serial number, date, detail, deposit and withdrawal. The report is generated to show the list of transactions.
However, I want the 'balance' to be calculated and seen at end of every transaction. (Kind of running balance, usually seen in a bank passbook or ledger)

For example -
Detail. Withdrawl. Deposit. Balance
B/F. 1000
200. 800
100. 700
150. 850

And so on....
There will only be either deposit or withdrawal in any transaction and not both.
I am having problem in getting the balance field. Can anyone help me.
Thank you.





 

plog

Banishment Pending
Local time
Yesterday, 21:16
Joined
May 11, 2011
Messages
10,258
The table table would contain 6 fields. Serial no, date, detail, deposit, withdrawal and balance.

Stop right there, that's wrong.

1. Credits and debits go into the same field. So instead of a deposit and a withdrawal field you use a TransactionAmount field and use negative numbers for withdrawals.

2. You don't store calculated values. That means the balance field comes out as well. When you need the balance you simply build Totals Query and add up that one TransactionAmount field. When you need the latest balance you reference that query.

So, the first step is to fix your tables to the appropriate structure. Then, if you truly need the balance after every transaction you should search this forum for "Running Balance Query". It has been solved multiple times.
 

Cronk

Registered User.
Local time
Today, 14:16
Joined
Jul 4, 2013
Messages
2,456
Also, how are you going to order your transactions to replicate the bank statement where multiple entries occur on the same day? You will be able to match the end of day balance but without recording some sort of order with the transactions, the intra day transactions will not match the bank statement.
 

Micron

AWF VIP
Local time
Yesterday, 22:16
Joined
Oct 20, 2018
Messages
3,471
I haven't done much in the way of accounting/ledger type of db's. It's primarily been ISO systems, training, contracting, maintenance and the like, hence the question: what is so wrong about having debit and credit fields? I realize it's not 100% normalized and each record would have a null in one field, but what's so bad about that? You can still sum the debits and credits and calculate the balance on the fly. You can still have multiple transactions on the same day (in another record of course). However, if you want to see them in a form as you would a ledger, then you have to crosstab query them when you could just store them that way? Worse yet, if you transpose the data, you can't edit it as the query would be not updatable, yes? This seems to me to fall into the realm of "normalize until it hurts, denormalize until it works". No doubt you've seen that before. As I said, it's not my area of expertise, but it seems like a case where rigidly following the doctrine brings more work for no real payback. Or is there something that you cannot do with a 2 field transaction setup?
 

plog

Banishment Pending
Local time
Yesterday, 21:16
Joined
May 11, 2011
Messages
10,258
what is so wrong about having debit and credit fields? I realize it's not 100% normalized

Asked and answered.

it seems like a case where rigidly following the doctrine brings more work for no real payback. Or is there something that you cannot do with a 2 field transaction setup?

SELECT Account, SUM(TransAmount]) AS Balance FROM YourTable GROUP BY Account;

I understand that with your version you substitute [TransAMount] with ([Debit] + [Credit]), but mine is simpler at no cost. Additionally, your method probably wastes space because one of your fields will be blank/0 when the other field contains a value. Also, Its not like my method is more taxing in the beginning then over time you recoup that effort. It's real, immediate payback, granted both are relatively small advantages in the grand scheme of things, but the right way is simpler and simpler is always better.

Take it a step further--why not seperate tables for credits/debits? That's a tad more painful to work with than your seperate fields, but not horrible more painful (I think even a peson new to Access could hack that to work). Lastly, I think we should always err on the side of recommending posters do things properly. Hacks should only be offered as a last resort.
 

Cronk

Registered User.
Local time
Today, 14:16
Joined
Jul 4, 2013
Messages
2,456
I agree with plog. The field is for "Transactions". The only difference between a debit and credit is that one is a negative amount. Other fields typically in accounting databases are AccountCode and CostCentre.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:16
Joined
Feb 28, 2001
Messages
18,705
Or is there something that you cannot do with a 2 field transaction setup?

Yes. Search for discrepancies or single values in a single field. You have to look in TWO places. OK, you would say "visually, they are right next to each other on the datasheet." But Access knows "NOTHING" about adjacency in that sense of the word. Two places to examine is still two places to examine, whether they are in separate tables or the same table. Programmatically, it is double the effort to have to maintain two fields rather than one for ANY query that needs to look at credits AND debits.
 

Cronk

Registered User.
Local time
Today, 14:16
Joined
Jul 4, 2013
Messages
2,456
A transaction can have multiple debits and/or credits where it's the total of both that have to equal. For example, a single payment of a purchase of hardware and software where separate accounts are kept for both.

Balancing of a transaction is enforced at the form entry/edit level.

For display purposes, a query like the following will sort the values into debits/credits
select iif(Amount)>0, amount, Null) as Debits, iif(Amount<.0, -amount, Null) as Credits from tblTransactions
 

Gasman

Enthusiastic Amateur
Local time
Today, 03:16
Joined
Sep 21, 2011
Messages
7,470
I use one field for my charity DB for the amount and another to indicate Payment/Deposit to help me, however the 'professionals' that are creating our new CMS web system have an option button for debit or credit and individual controls for each type of amount? Not had the chance to see what data validation they have in place though. :)

This has yet to go live yet though. :)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 03:16
Joined
Feb 19, 2013
Messages
12,756
since this thread has been referenced you can also use the format property for each column using the same controlsource

dr...............................cr
0.00;""......................"";0.00
 

Users who are viewing this thread

Top Bottom