Calculationg the difference in two values

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a table with two fields, txtvalue (a number field) and txtmonth ( a
date/time field). I want to create a report that shows the differerence in
value between the value in txtvalue in one value of txtmonth and the value
of txtvalue in another value of txtmonth and the percentage increase . For
example I have the value 1000 in 30/03/03 and the value 1100 in 30/03/04 How
do I calculate the difference as 100 and the increase as 10%
TIA
Tony Williams
 
Tony said:
I have a table with two fields, txtvalue (a number field) and txtmonth ( a
date/time field). I want to create a report that shows the differerence in
value between the value in txtvalue in one value of txtmonth and the value
of txtvalue in another value of txtmonth and the percentage increase . For
example I have the value 1000 in 30/03/03 and the value 1100 in 30/03/04 How
do I calculate the difference as 100 and the increase as 10%
Click to expand...


The key to doing complex reporting is to create a query that
organizes the data per your description. After that, the
report should be simple.

I think you can get started with a query along these lines:

SELECT A.txtMonth,
A.txtValue,
B.txtValue As PrevValue
FROM thetable As A LEFT JOIN thetable As B
ON DateAdd("yyyy", 1, B.txtMonth) = A.TxtMonth
WHERE ????

Now the report can use simple text box expressions.
txtDifference: =txtValue - Nz(PrevValue, 0)
txtPercentage: =txtDifference / txtValue
 
Back
Top