Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel does this easily but not Access!

    I am trying to set up a query which calculates the total depreciation of each asset in an asset table over a number of consecutive financial years. I am using the reducing balance method which calculates the pro-rata depreciation of each asset each financial year with a starting value in each year equal to the closing value in the previous.
    So, mathematically, you have a "piece-wise" linear function.
    In Excel it was easy.
    Your assets table has colums for
    purchase price,
    purchase date,
    sale price (if any),
    sale date( if any),
    depreciation rate % per year,
    and a row for each asset.

    From this you could contruct an Excel list where the depreciation could be calculated for each asset and shown in its own column. Then you get the closing value by
    subtracting the depreciation from the opening value for the year in question. For each new financial year I created a new page in the workbook and transferred the closing value over to the new year's opening value for each asset.
    Now!
    I wan't the whole thing to now work in Access.
    You can create a query based on the Assets table but
    at some point I keep coming up against the problem of taking
    values from previous rows of the query to use in the current row. I read and applied the Microsoft KB article
    "Q210504 - ACC2000 Referring to a Field in the Previous Record or Next Record.htm" which was enlightening but doesn't seem to lead to a solution.
    Has anyone any ideas how to proceed?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel does this easily but not Access!

    When I need to do this, I use VB. I can hold the current record values in variables and then use .MovePrevious to collect the previous values, like this ...

    'collect values from current record
    vTruck = rsFuel![Truck]
    vDriver = rsFuel![Driver]
    vDate = rsFuel![Date]
    vOdometer = rsFuel![Odometer]

    'collect values from previous record
    rsFuel.MovePrevious
    vPrevTruck = rsFuel![Truck]
    vPrevDriver = rsFuel![Driver]
    vPrevDate = rsFuel![Date]
    vPrevOdometer = rsFuel![Odometer]

    Now, I have the values from both records and can perform the calculation.

    There may certainly be a more efficient way to accomplish this, but this has worked for me.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel does this easily but not Access!

    Thanks I was aware of this method but the trouble is that in my situation you are not able to use the MovePrevious method because it is not necessarily the previous record in some table. It is the previous record in a special query I have set up or, putting it another way, I want to move to the record in this query where the Asset ID is the same as the current record's and the Financial year ID is one number less. This is more like a filter in way. I then want to take the values of certain fields and use them in the current record. But I need to clone the recordset of the query somehow because I want to keep the current record current. Each row of the query must consult certain other rows to obtain the closing price at the end of each financial year. It is a bit daunting! Now I am trying another approach using a mathematical formula instead.

  4. #4
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel does this easily but not Access!

    I was looking for the same capability this week for a one-time calculation.

    I found it 'in Access' as the RunningSum property(?) of an expression in a Report.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel does this easily but not Access!

    You probably can make use of this Running Sum property but I want to be able to display a query showing the depreciation of each item in the list for any given date.
    I have just about solved the problem by creating a User Defined Function in a module which basically loops through all the financial years that the asset is possessed and does a "Running Sum" of the depreciation until the item is sold or we reach the current date. It is working but I need to check for accuracy. If anyone is interested in this Function I will post it in this thread soon. You could apply it in widely varying circumstances.By the way, our Financial year in Australia is July 1st till June 30th the following year. Weird isn't it? What is it in the States?

  6. #6
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel does this easily but not Access!

    I'd be interested in seeing your function.

    FY is up to the individual/corporation. The 'default' is the calendar year, but one can declare it to be anything convenient. At the same time, 'adjustments' need be made to bring nay self-declared FY into alignment with the calendar.

Posting Permissions

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