Debit, Credit & Balance calculation in MS Access

[Zalfa Arif]

Dears,
I have one query in MS Access with date, debit & Credit fields as showing below.

Date Debit Credit
01-08-2010 0 1000
02-08-2010 500 0
03-08-2010 0 2000
04-08-2010 1500 0

Kindly help me anyone to find below three things.
1. How can we get the balance as per debit & Credit rules as given below?

Date Debit Credit Balance
01-08-2010 0 1000 1000
02-08-2010 500 0 500
03-08-2010 0 2000 2500
04-08-2010 1500 0 1000

2. How can we get the opening balance?
3. How can we show debit, Credit & Balance within selected dates together with opening balance in a Form or Report ?

[beacon]

Hi Zalfa,

For #1, here's a link that talks about running totals, which you can use for your balances: http://articles.techrepublic.com.com...1-6140569.html.

You will likely need a form to select a specific person and then have the ID associated with that person passed to a query to filter the records when the form you're using to display the balances opens.

So, two forms, one to select the person you want to view the balances for and another to display the balances. The first form passes the ID for the person into the query and calls the second form, which opens with the records filtered for the person selected.

For help on using a form to pass custom parameters, view this link: http://www.fontstuff.com/access/acctut08.htm

For #2, in the Load event of the form, use DAO to create a recordset clone and traverse to the first record, then set a textbox on the form equal to the pointer.

Here's an example...it assumes that you have a textbox on your form called txtMyBalance and a field in your query called Balance:

Private Sub Form_Load()
 
    'Make sure you have DAO 3.6 selected in your references
    Dim rst as DAO.RecordSet
    Dim myRecord as Long
 
    Set rst = Me.RecordSetClone
 
    With rst
        .MoveFirst
        myRecord = .RecordCount
    End With
 
    txtMyBalance = = rst![Balance]
 
End Sub
 

For #3, you'll have to do a combination of #1 and #2. You'll need to create a form that accepts date parameters and use this to filter the query that is the record source for your form that displays the balances. If the opening balance may not be included in the date range, you may need to create a separate query that returns all transactions for a person, so that you can grab the opening balance.

Hope this helps...

beacon

[Zalfa Arif]

Dear Beacon,
For #1: As per your instruction I have successed in finding balance of Credit&debit in query. But still there is problem when I'm filtering. I have another filed also in this query which is called as 'Account Type'. The filed 'Balance' is calculating hidden records also when I'll filter 'Account Type' filed

[beacon]

This may or may not be true, but I think you will likely need a separate query for each 'Account Type' or you will need to make the query parameter-based as the records will have to be filtered by the account type prior to compiling the records.

If your goal is to ultimately output the data to a form, the parameter idea (that I provided as a link in my first post) would be ideal.