1. Registered User
Join Date
Aug 2006
Posts
5

I am trying to creat ledger accounts in a small access applications. What should I do if I want to creat opening balances and closing balances for periods - For example if I give a date range of 10th February to 20th February - the report should show the openingbalance as on 10th February and closing balance as on 20th February - Can any one help me?

2. Registered User
Join Date
Jan 2007
Location
California, USA
Posts
520
Based on the very little detail given, I would suggest a summary query on the ledger from the beginning until Feb 9th for the opening balance. Then for the closing balance, a summary query on the ledger from the beginning until Feb 20th.
HTH,

3. Registered User
Join Date
Aug 2006
Posts
5

## Opening/Closing Balance

The detail is here. My ledger looks like the one given below When I take the yearly ledger I am getting opening balance fine but if I want the ledger between 9th February and 20th February - then I want to get the opening balance as on 9th February and closing balance as on 20th February. I mean I want my report to reflect the opening and closing balances period entered as criteria.. Can U help me

Date Doc. No. Debit Credit balance
Opening Balance 0
08-02-07 1 20 0 20
08-02-07 2 0 10 10
09-02-07 3 21 0 31
09-02-07 4 0 11 20
12-02-07 5 31 0 51
13-02-07 6 40 0 91
20-02-07 7 0 45 46
21-02-07 8 0 12 34
22-02-07 9 50 0 84
Closing Balance 84

4. Registered User
Join Date
Jan 2007
Location
California, USA
Posts
520
Actually, what I suggested before still holds true. A summary query of your ledger from the beginning until Feb 9 (or Feb 10 in first post) will give the opening balance, and then another query over the ledger from the beginning throughl Feb 20 will give the closing balance. The only thing you need to do is one calculation in the query because you have the transaction amount in two separate fields. Therefore, you can sum the calculation of [Debit] - [Credit].
This would cause the first query (opening balance) to sum the following values: 20, -10, 21, -11. The second query (closing balance) would sum those figures, plus these: 31, 40, -45. The -12 and 50 would not be summed as they are after Feb 20th.
HTH,

5. Registered User
Join Date
Oct 2002
Location
Leicester - UK
Posts
820
The only ways to get this data is to Calculate it on the fly as GolferGuy suggests or to store the data in a table, if your ledger is small ie less than 100,000 record then on the fly will probably serve however more than that i'd suggest shifting to stored data

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•