Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Bank Balance (xp/2k)

    I am sure someone has thought this through. I have a form that is the income and outgo of a balance of money on hand in a business. It is driven by the sales and the receipts that are printed. Thus all the income is input automatically. The outgo - are the deposits to the bank (don't ask that is how it works). It needs to keep a balance. There is a continous form which lists all the transaction and maintains a balance. The form can be filtered to start and end at a specific date. There needs to be the option of adding the deposits NOT IN ORDER and then calculate an accurate balance. It then needs to put in the right order and redo the balance for when the deposit was made, even though in the interim other transactions took place. As we all know there is no running sum on a form. Therefore, I have been keeping the balance in the table (horrors!) and when there is an out of order transaction, through code recalculating the whole thing. IT also needs to print a report with te opening balance of a specific day and a running balance. I do not think i can use running sum, since it has to start from the opening balance and work from there. The problem is the report is not accurate since it does not always put the transactions in the correct order so the balances from the table are off.

    I am sorry about the length, but if you understand and have some ideas I would be grateful.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bank Balance (xp/2k)

    Zave,
    How is a transaction noted as *out of order*? Are the transactions sorted by an ID or by date? I'm shootin in the dark here, but depending on the "proper" location of the transaction, couldn't you use a sort by date/time, or add the transaction by ID, and run an update query to add 1 to every ID greater than the one you're inserting (to ensure proper order)?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bank Balance (xp/2k)

    they are by date not time. I was tryig to geet around using a numerator, but I do not see any way out

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bank Balance (xp/2k)

    they are by date not time. I was trying to get around using a numerator, but I do not see any way out.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bank Balance (xp/2k)

    Okay, okay, I get the point..no need to tell me twice <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>.

    If you're trying to get away from a numerator, how are your transactions ordered right now? Is there no transaction ID or anything?
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bank Balance (xp/2k)

    Zave

    Sorry to jump in here half way through a thread.

    I have attached a simple bank account database I created about 12 months ago as I was trying to keep my head around 4 savings accounts we have. I have removed the amount of money I have in the accounts as I think you would be jealous how many millions of pounds I have <img src=/S/liar.gif border=0 alt=liar width=25 height=22>

    I created a table called tblAssets and a look up table for my accounts/banks. The Input form is for putting in which credit or debit I am dealing with in transaction order, not necessarily date order just when the statements were dated. This is important assome paid quaterly interest the other annually and the other 2 monthly.

    The input form shows transaction details but the other form you can detail the cash handled in each account by selecting the drop down. I created ( and it seems to have gone pear shaped as this was an old development one of mine but you could get ideas from it) a report that showed the transactions sorted by date and account to keep an eye on each asset.

    Hope this helps
    Jerry

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bank Balance (xp/2k)

    sorry about the 2ing. There is nothing other than the date and the order they are entered

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Bank Balance (xp/2k)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>

    I'm not sure what you can do about the problems in the past, but to resolve future issues you could use an ID or change the date block to date/time, so that if you enter a transaction late, it will still fall in place.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bank Balance (xp/2k)

    Another alternative, and this is how I got around by Asset database is to have an Excel work book that is connected via the database to the tblAssets table.

    I was able to refresh the data each time I opened my excel workbook allowing me to create running transactions. With manipulation in the workbook, I could create running totals, sub totals, graphs of asset rise and lows. I took this step as I found it easier to manipulate the workbook for accounting than manipulating the access tables in queries. My <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth
    Jerry

Posting Permissions

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