Full Version: Balance Sheet Formula
UtterAccess Forums > Microsoft® Access > Access Forms
mkkashif
Dear Please Help Me
I have table of following fields
redit Debit Balance
200 0 200
0 10 190
0 20 170
100 0 270
How Can i Create like this Balance Sheet in Access.
How Can i create in Query or Form(DataSheetView) What Condition has to apply
to balance field.
Maurie
Welcome to Utter Access!!
For a balance sheet Assets - Liabilities = Equity
What you need to do is add all the Assets and then all the Liabilities and subtract one from the other.
A trial balance would be different. You would list all of the Accounts and their balances. The total of debits - credits should be 0.
Based on your example, it appears as though you are looking to maintain the balance for an individual or customer. Purchased 200, paid 10 then paid 20 then purchased 100 leaving a balance due of 270. If
that is the case, then you are not looking to produce a balance sheet.
mkkashif
Dear
I want the ladger of the customer .a complete detail of customer
How can i calculate this .Balance Field.
Can you tell me that where can i find the example file like that i am doing.

Credit Debit Balance
100 0 100
0 10 90
200 0 290
How can i do it in access like that
preston
To correctly portray the ledger of a customer, unless you want to display Life To Date in every case, you need to restructure your db.
irstly, you don't need Balance as a field. The Balance would be calculated. Secondly, my preferred way to do it is with a tblTransactions, that consists of TransactionID, TransactionTypeID, TransactionAmount and TransactionDate. TransactionTypeID would relate to a tblTransactionTypes that include TransactionTypeID, TransactionType, and what I usually call CRFlag, where this field would be TRUE if the Transaction typer were a natural credit and FALSE if the transaction were a natural debit.
So to do what you are trying to do, you would use DSum to calculate the Beginning Balance by summing iif([CRFlag]=True, TransactionAmount, -TransactionAmount) where date < Your ReportBeginDate
For the debit and credit fields, you use a query containing TransactionTypeID, TransactionAmount and CDFlag. In two textboxes on your report.....For the Credit, you use the expression =iif([CRFlag]=True,[TransactionAmount],Null) and for the Credit, you use the expression =iif([CRFlag]=False, [TransactionAmount],Null)
For the Running balance, I'd calculate =[BeginningBalance=+iif([CRFlag]=True,[TransactionAmount],-TransactionAmount]
hth
mkkashif
Dear Sir
if you can easily transalate your answer i will be very thankfull to you because i think your information will done my work.
explain my db.i have a query with customerid,which is linked one to many .then Credit Field Debit Field and then a Balance Calculated Field.I am doing in Query Balance as expression Balnce:Credit -Debit.
it is all i did.So What i do now Please told me by my Field Name.Thanks Very Much.
mkkashif
Here is the Solution
but i dont know how can i make it My Solution
Need Help
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.