calculate results between previous records

[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 Dybenko]

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

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Jason]

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

Hi Alex,

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

 

[David Cox]

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

 

[Alex Dybenko]

Hi,
David already posted one

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com