how to calculate yearly trends in access

[Blerta]

Hi,
Please can anyone help me with this issue? I have a variable(Revenues) and revenues over 4 years in 4 fields(rev_01,rev_02,rev_03 and rev_04) I want to calculate the yearly trend(rev_02-rev_01)/rev_01. Till now everything is easy but the problem is that my data are in this way:
Rev_01 Rev_02 Rev_03 Rev_04
123 457 0 0
0 0 145 759
0 657 413 0
I want to calcuate trend over years when revenues are <>0.
Thank you.

 

[dhookom]

I'm not too excited about your table structure since it isn't normalized.

However, you can use IIf() to check if the Rev is 0. I assume you could use something like:

IIf(Rev_01 * Rev_02 = 0, null,(Rev_02-Rev_01)/Rev_01)

Duane
Hook'D on Access
MS Access MVP

 

[alvechurchdata]

I agree with Duane that life would be easier if the data were normalised.

I think you're going to have to open the data as a recordset and then run two loops, one to step across the fields of each record (always keeping the value from the previous field in memory) and another to step through the rows of the recordset.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Blerta]

Thanks to all of you for your help,
I used the statement above and it gave me the correct result.
Thanks