Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    5

    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?

  2. #2
    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. #3
    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. #4
    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. #5
    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
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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