moving range

[anzacks]

Hi all,

I'm new in this forum and can be considered as beginner in Ms Access. I tried to make a SPC database and currently stuck at calculating the data range for moving range graph.
The question is, how to calculate range between 2 consecutive data and then sum it.
For example:
A, 195
B, 198
C, 196
D, 194

Thus the range between A and B is 3 (198-195), B and C is 2 (198-196), C and D is 2 (396-194). The total range is 7 (3+2+2)

Anyone has the idea?

Thanks.

 

[Uncle Gizmo]

Have a look for posts on "running sum" and I believe there is an example in Roger Calstons web site.

 

[anzacks]

range

Have a look for posts on "running sum" and I believe there is an example in Roger Calstons web site.

Hi Uncle Gizmo,

Thanks for your advice. I went through some post about running sum, but so far I can't found something like I need.
I need a function that can calculate range before sum the result.

For example:
No, Data
A, 195
B, 198
C, 196
D, 194

I need a function that can create a column for range like below:
No, Data, Range
A, 195, 0
B, 198, 3
C, 196, 2
D, 194, 2

Thanks.

 

[Uncle Gizmo]

>>> the range between A and B is 3 (198-195) <<<

Sorry I'm not familiar with using the "running sum" example provided by Roger, I have used it in the past, but that was years ago, and at the moment I don't have the time or inclination to revisit it.

The reason I pointed you in the direction of the "running sum example" is that the running sum adds the previous value to the next value, (I believe) and in your case you need to reverse this, perform a subtraction instead of an addition.

Indeed I may be missing some obvious fact, because I haven't actually looked at the example lately, but its the first thing I would try if I was doing this myself. I suggest you have a look, if you can't make it work then come back with some more questions.

 

[gemma-the-husky]

the only way to do this i would think is to iterate a recordset.

get your table or query and then write code similar to

myrst = currentdb.openrecordset("mytable")

while not myrst.eof
{process the item here
eg save details to compare with next one
do any calculations you need
etc}

myrst.movenext
wend

 

[anzacks]

range

the only way to do this i would think is to iterate a recordset.

get your table or query and then write code similar to

myrst = currentdb.openrecordset("mytable")

while not myrst.eof
{process the item here
eg save details to compare with next one
do any calculations you need
etc}

myrst.movenext
wend

Thanks for the advice. I will try that.