Query help needed please

[Qwerty8989]

Hello everybody,

I am unsure whether this is best done in SQL or VBA, however, I do believe it is possible in SQL so I will put it here.

I have a field 'value of purchase' in which an items cost is inserted. What I am wanting to do initially is automatically deduct 20% off the value of purchase every year until it reaches zero. Now I don't want to deduct 20% off the new value every year, for example:

The price is $1000, 20% deduction is $800. But then year 2 it will be $600 then the next year $400 and so on. So I suppose the first year 20% is taken off the original price, then for year 2 40% and so on (always off the original price not the newly found values).

I want to display this in a new field 'current price'.

I am just not sure how to create the query so it will automatically deduct these amounts per year. If anybody has any advice/help it is muchly appreciated.

Thanks

 

[Michael J Ross]

Hi, you need to use Iif and DateDiff, add a calculated field as below e.g.

CurrentPrice:Iif((DateDiff("d",Purchasedate,Date())>365 And <731,[Value of Purchase]*0.8),Iif(DateDiff("d",Purchasedate,Date())>730 And <1096,[Value of Purchase]*0.6),[Value of Purchase])

This does it for the first two years so you get the idea of how it works.

 

[Qwerty8989]

Thank you very much, sorry for the late gratitude response, I had computer issues ^_^