If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Access asset tracking template: depreciation
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! |
#2
|
|||
|
|||
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 "Jack Uvaltraids" Jack wrote in message ... 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! |
#3
|
|||
|
|||
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 "Jeff Boyce" wrote: 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 "Jack Uvaltraids" Jack wrote in message ... 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! |
#5
|
|||
|
|||
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 "Jack Uvaltraids" Jack wrote in message ... 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 "Jeff Boyce" wrote: 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 "Jack Uvaltraids" Jack wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I access a created template from letter wizard | trying to streamline | General Discussion | 6 | January 5th, 2005 02:52 AM |
Images in a database | Franz | General Discussion | 10 | October 7th, 2004 09:35 AM |
default font when designing reports and forms | Karen | General Discussion | 2 | August 25th, 2004 04:44 PM |