Rookie needs some help with Depreciation

[Denise]

Please let me know if I posted this in the wrong place...

Today is my first day with Access. I downloaded a
template to track my assets and track depreciation.

I am entering each piece of my equipment and all the
info. I'm doing fine so far.

I also downloaded an Excel template that automatically
calculates Single Line depreciation:
D6/D7/D8 are Initial Cost/Salvage Value/Useful Life
Heading of B is "Years" - Heading of D is "Strait Line"
b14 is =IF(D8,1,"")
d14 is =IF(B14>0,SLN($D$6,$D$7,$D$8),"")
the rows auto calculate for each year until the useful
life is done
total is at the bottom

Can I replicate this report in Access? There is a summary
report in the template that has a Cost column and the
Accumulated Depr. from the forms but the depreciation
calculation would make the report exactly what I need.

Any help for this eager to learn rookie would be
appreciated. Thanks!!

 

[SA]

Denise:

Assuming that you have the initial cost/ salvage etc. values in a table
accoring to fields, then sure you can do this in Access. The trick of
course is to also have a date field for asset acquistion and you'll need to
compare that to the useful life field value to only pull those assets that
still require depreciation in your query. (Use the datepart and datediff
functions in a field in your query to calculate where you are on a year
basis compared to the acquisition date and useful life, as in
PullAssetRecord(DateDiff("m",[AssetAquisitionDateField],Date()))\12)<[UseF
ulLifeField] ; with a condition of True in the conditions row; use the
months \ 12, to get full years, if you use DateDiff with "yyyy" as the
comparison, it will only compare the year value of the dates which can
overestimate the difference).

Notwithstanding that , you can then use IIF() statements in your query or
your report (the report will run faster if you do them in the report,
allowing the query to simply pull the data.) to compare the values in the
various fields and do your depreciation calc. You'd use the control source
for an unbound control to do this calc, as in = IIF(SomeStatementIsTrue,
ThenThisValue, OtherwiseThisValue)

I've given you clues as to how to do, you'll have to work it from there.

 

[Guest]

Thanks Steve. I'll print your reply and put on my
thinking cap. Access terminology is new to me so I'll
have to comb through it slowly - thanks again!

-----Original Message-----
Denise:

Assuming that you have the initial cost/ salvage etc. values in a table
accoring to fields, then sure you can do this in Access. The trick of
course is to also have a date field for asset acquistion and you'll need to
compare that to the useful life field value to only pull those assets that
still require depreciation in your query. (Use the datepart and datediff
functions in a field in your query to calculate where you are on a year
basis compared to the acquisition date and useful life, as in
PullAssetRecord(DateDiff("m", [AssetAquisitionDateField],Date()))\12)<[UseF
ulLifeField] ; with a condition of True in the conditions row; use the
months \ 12, to get full years, if you use DateDiff with "yyyy" as the
comparison, it will only compare the year value of the dates which can
overestimate the difference).

Notwithstanding that , you can then use IIF() statements in your query or
your report (the report will run faster if you do them in the report,
allowing the query to simply pull the data.) to compare the values in the
various fields and do your depreciation calc. You'd use the control source
for an unbound control to do this calc, as in = IIF (SomeStatementIsTrue,
ThenThisValue, OtherwiseThisValue)

I've given you clues as to how to do, you'll have to work it from there.
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

Please let me know if I posted this in the wrong place...

Today is my first day with Access. I downloaded a
template to track my assets and track depreciation.

I am entering each piece of my equipment and all the
info. I'm doing fine so far.

I also downloaded an Excel template that automatically
calculates Single Line depreciation:
D6/D7/D8 are Initial Cost/Salvage Value/Useful Life
Heading of B is "Years" - Heading of D is "Strait Line"
b14 is =IF(D8,1,"")
d14 is =IF(B14>0,SLN($D$6,$D$7,$D$8),"")
the rows auto calculate for each year until the useful
life is done
total is at the bottom

Can I replicate this report in Access? There is a summary
report in the template that has a Cost column and the
Accumulated Depr. from the forms but the depreciation
calculation would make the report exactly what I need.

Any help for this eager to learn rookie would be
appreciated. Thanks!!

.