Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Calculation (2000)

    I have a construction database with 2 tables. 1 is the BuilderInfo. The 2nd is the "status" for each property the builder is building and includes a record for each draw amt from the building loan amt and the % complete of the building.

    I need to be able to keep a running calculation of the Loan Amt. The 1st draw comes directly from the original loan amt.

    The next draw needs to come from the "loan amt left * % complete", because that's the max amount they can draw the next time. So I need an "Outstanding Loan Amt" calculated each time there is a draw and I need to use this amount in a calculation the next time the builder needs a draw.

    I have tried lots of different things and can't find a way to keep the calculation for the next record. They would like this to be on a form, so they can see the balance as they are entering the draw amts, etc. If it can't be done on a form they might have to live with it on a report, but I can't figure out a way to get it on a report either.

    Thanks in advance for any ideas!
    Patty

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Calculation (2000)

    Patty,

    It sounds like you need to sum up all the draws for one building and subtract this from the original loan amount. How you do this depends on where you keep your data. You say each record in your second table contains the draw amount and the % complete. If it can also include (or already does include) the building the draw applies to and the original loan amount, then a totals query on this table (grouped on the building/loan ID) would provide the necessary source data for a calculated field in your form.

    I'd suggest you consider a third table that contains the loan and/or building information (which would include the original loan amount, a reference to a record in your BuilderInfo table, the building description, etc.). Each record in your "draw" table could then reference the appropriate record in the loan/building.

    This is a bit sketchy, but hopefully it gives you an idea of how to tackle this.

  3. #3
    Star Lounger
    Join Date
    Aug 2002
    Location
    Wichita, Kansas, USA
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Calculation (2000)

    Thanks for the info Tom. I need to get a better understanding of the totals queries - I'll have to dig into a book on them.

    Late yesterday afternoon I did find a way to do what I needed by putting some VBA code behind the % complete field after data was entered into it - have it auto calculate and plug the remaining loan amount back into an outstanding balance field I added to the first table. I think my first table is setup the way you are suggesting for the 3rd table.

    Patty

Posting Permissions

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