calculate results between previous records

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hi,

Can somebody help me with the following:

I have a table with some numbers and dates. I want to calculate the
difference between the dates and numbers and put that in a table or query.

For example:

field1 field2 field3
abcd 01-01-2004 1000
abcd 31-12-2004 2500
abcd 20-03-2005 2580

i need to know how many months in field2 between the first record and
the next, also for field3.

the result should be:
field1 field2 field3
abcd 11 months 1500
abcd 2 80

I think this should be done in vb, but i how do i start?
 
Alex said:
Hi,
to get number of months you can use DateDiff() function. so you can
write a function, which will get a previous record (based on field2
order perhaps) and calculate difference
Click to expand...
Hi Alex,

Do you have an example for me using previous record and next record?
 
something like:

SELECT Table1.testdate, Table1.testvalue, [testvalue]-(Select [testvalue]
from [Table1] as X WHERE [Testdate] = (select max(Y.[testdate]) from table1
as Y where y.testdate < [table1.testdate];);) AS difprev
FROM Table1
ORDER BY Table1.testdate DESC;

There is probably a better way
 
Back
Top