calculated field problem....

[kimmole]

here's the problem......

i have a set of user entered fields;

[capital item name]
[start price]
[start date]
[tax year end] (that's the tax year end applicable to the item)
[write off amount year 1] (percentage of start price to write off in year 1)
[write off amount year 2 and on]
[check box] to indicate weather it's a proportional write off or not. (this means that if in tax year one the item is only owned for say 10 days the write off is only a ten day proportion of the yearly amount. and iff the item is not proportional the full percentage is writen off in year 1).
i also have another table which contains a date field for the [tax year now being worked with].

i need to be able to calculate the amount to write off for the tax year being worked with.
ie.
iff the item is not yet purchased (the [tax year now being worked with] is prior to the [start date],=0

iff the item is in tax year 1 (relative to the tax year being worked with) And [check box]=0,([start price]/100)*[write off amount year 1]

iff the item is in tax year 1 (relative to the tax year being worked with) And [check box]=-1,(([start price]/100)*[write off amount year 1])*"number if days owned")

iff the item is now in it's 2nd tax year since purchase (relative to the tax year being worked with)
it would be the result of the ("residual balance from year 1"/100)* [write off amount year 2 and on]

iff the item is now in it's 3rd tax year since purchase (relative to the tax year being worked with)
it would be the result of the ("residual balance from year 2"/100)* [write off amount year 2 and on]

........ etc.
....now i can make all this happen but only if i pre calculate all the fields for say ten years and then work out what tax year i'm working with... what i want is to be able to have only one resultant field which calculates a value relative to [tax year now being worked with]....

can anyone please help me as i get lost with DatePart and DateDiff calculations really quickly.

ps. as an asside;
could somone jot down how to make a query that either allows me too enter the data in one field and have another calculated or enter the data in the field and have the result calculated for the other end?

hmmm.... not to clear there...... so... field [a] is either entered or is calculated as the result of say +[c]... but if [a] is entered then [c] is the calculated field. ......make any sence?

thanks in advance.... kimbo))

 

[OhioSteve]

In my opinion, your first task is to separate calculated and fixed fields. In most database designs, calculated fields are not stored. Instead, you store the components and the rules for the calculations.

For you it may be challenging to implement this principle. I assume that you are calculating values for taxation. For future years, you want to project depreciation using specific rules (so you DON'T want to store those values). For past years, you have already paid taxes. So the rules used to calculate those depreciation amounts may be irrelevant, because its too late to change things now. It may be appropriate to hardcode those amounts in tables.

After you decide which results to hardcode and which to calculate, you can begin to actually write queries, create tables, etc.

 

[kimmole]

exactly right.... the fields i've got stored are as in the original posting and the calculated fields are only for display purposes. i'm trying to write it so that i dont store the data from calculated fields but only display them on the forms as i need to.

 

[OhioSteve]

Well, I think that I can help you if I can understand the process.

You have a list of capital assets. Each on has a name, purchase date, an initial cost, and a gross amount of tax due. Also, each one depreciates in either a porportional or a non-porportional way.

In a addition, you know that assets depreciate a certain amount each year. These amounts are expressed as percentages. For example, the rules might say "in year two assets depreciate 10%".

You want to calculate the net amount of tax due. To find that amount, you take the gross taxes and subtract (initial cost * depreciation percentage). So if an asset originally cost $20, you owed $3 in taxes, and you could depreciate 10%, then you would owe $1 in taxes ( 3-(20*.1)=1).

Let me know if that is accurate.

 

[kimmole]

no...
i know the purchase date
the amount
the tax year end relative to the item in question
the depreciation applicable in year 1
the depreciation applicable in year 2 and on

in a seperate table a year end date to work with is entered and i need to calculate the write off sum applicable for the year to work with ....in other words i may be evaluating figures for the second year or i may be reviewing figures for a year previous to the purchase for some items.