If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Opening/Closing Balance

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?

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,

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

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,

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