Access asset tracking template: depreciation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there any way for Access to automatically post depreciation amounts every
month? It seems that it would be a massive task to type in depreciation
amounts for every asset every month. I would think that if I gave Access the
original value, the depreciable life, and the depreciation method it would be
able to update the depreciation schedules each month. Thank you!
 
Jack

How would you do this in a spreadsheet?

Are you aware of an "automatic" way to do this with a spreadsheet?

Why have you decided that Access is what you need to do this with?

Why would Access "know" what to do?

I suspect you'll find that no matter whether you use paper/pencil, Excel,
Access, or some other tool, you have to "do the math" to make it work.

What have you already tried?

Good luck

Jeff Boyce
<Access MVP>
 
Hi Jeff,

Thanks for your help! I am familiar with how to create a depreciation
schedule in a spreadsheet, but I am new to Access. I chose to track my
company's assets in Access because the number of depreciable assets we have
is significant (ballpark 2500) and the amount of data we are required to
record for each asset is considerable (I work in a pretty highly regulated
industry).

I know that Access won't "know" what to do and that I'll still have to "do
the math" - perhaps I didn't pose my question clearly enough.

What I meant is this: In Excel I can use formulas & expressions to pull
data from multiple cells and produce a desired result in another - like a
depreciation schedule. Since the Access "Asset Tracking" template already
has a depreciation table with fields for original value, depreciable life,
and depreciation method, I was hoping that there was some way I could create
a new field that would take the data from the other three and calculate, for
example, the dollar amount depreciated to date for a particular asset. The
way the template is built now a person would be required to manually enter
the $ amount depreciated each month for each asset - a lot of work in my case.

So far I have tried: 1. An "Access for Beginners" class that was a total
waste of time and money 2. I've tried using "Help" to find some sort of
formula and/or expression tutorial. There were a few good examples, but
nothing comprehensive. 3. I tried reverse engineering some expressions that
were pre-made in some Access templates but they were pretty much Greek to a
rookie like me. 4. "Access Step by Step" by Microsoft Press - again a few
good examples, but nothing comprehensive (the book was, however, worth every
penny of $24.99).

Maybe there's some key tool that I'm overlooking, maybe I expect too much of
Access, maybe it's just beyond my capabilities (as the saying goes: jack of
all trades, master of none). I'm sure there's no easy solution, just hoping
someone can point me in the right direction. Thanks again for your help!

Jack
 
Jack

If I were handed this assignment, I'd start out searching Google.com and
mvps.org/access, as well as Microsoft.com, to see if anyone has templates.

I would point out, though, that you DON'T want to store a calculation if you
can calculate it on the fly.

Access tables store data, forms (and reports) display it. Work up the
formulas, either in queries and/or using procedures, then display the
results in forms.

Good luck!

Jeff Boyce
<Access MVP>
 
yes, sounds like you almost all the raw data you need to calculate
depreciation. if you want to be able to calculate "the dollar amount
depreciated *to date* for a particular asset", you need one more thing - the
date depreciation starts, which i assume would be the date each asset was
purchased or otherwise acquired? i think that would pretty much have to hard
data, for each asset record.

the easiest way to write a calculation once, that you can use over and over
again, pretty much anywhere in the database, is to write a public function
that returns the calculated values. something like

Public Function isDepreciation(ByVal curOrig As Currency, _
ByVal lngLife As Long, ByVal datAcquired As Date) As Currency

'curOrig is the original value, lngLife is the depreciable life,
'datAcquired is the date acquired or purchased.
'inside this function, you write the calculation.

Dim intMonths As Integer

intMonths = DateDiff("m", datAcquired, Date)

'the following line will convert a "depreciable life" value of years into
months;
'if your "depreciable life" data is already expressed in months,
'you don't need this line of code.

lngLife = lngLife * 12

isDepreciation = (curOrig / lngLife) * intMonths

End Function

create a new Module in Access, from the Modules tab in the database window,
paste this function into it, and save the module. you can call the function
from within a query, a form, a report, or another VBA procedure - wherever
you need to return the current depreciation value of an asset.

hth
 
Back
Top