Calculating the difference in values in two different records

[Tskutnik]

Note sure what the best way is to do this.

I have a query that must calculate the different between values in two different records, based on the date.
The underlying ORIGVALUES table contains:
Date; Value; Diff
1/10/15; 100; (this is what I am trying to calc)
1/11/15; 101; (this is what I am trying to calc)
1/14/15; 102; (this is what I am trying to calc)

What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.

For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01)
For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)

I could copy the prior day values to the current day records and do that math easily but that is very redundant.

Any help is appreciated.

 

[Ranman256]

no record can see another in a query.
the only way would be to write code to
get a rec
get next rec
do the math
loop untiil done

 

[Tskutnik]

thanks. I sorta figured.

 

[spikepl]

Ranman256 was a bit hasty.

You can use a subquery - with few exceptions much faster than looping in VBA http://allenbrowne.com/subquery-01.html

Update

Ranman256: go easy on sweeping statements. Seldom is there only one solution to a given problem. If you know one that's fine that you supply it, but don't make it look as if that is the only one.

 

[Ranman256]

I didnt say it was the only solution.
I gave the general rule for a novice.

 

[KenHigg]

If you don't have a lot of records to traverse you could write a function to calculate it. Or you could simply store in another fld...