create query that adds last four numbers, divide by four

[dharkangel]

HI everyone,

I am wondering if it is possible to do the following in an Access query. I want to stay away from writing VB code if I can. I think the best way to describe is to show a sample.

Here is a sampling from my table. The left column are my existing values (CBC) and the right column (newValues), is what I would like to do:

(ID) (CBC) (newValues)
1 0
2 0
3 2
4 2 1
5 3 1.75
6 6 3.25
7 4 3.75
8 7 5
9 5 5.5
0 2 4.5

For example myValues entry number 6 would be calculated like this: (6+3+2+2)/4 = 3.25. Entry number 7 would be calculated like this (4+6+3+2)/4 = 3.75...

thank you

 

[mp9]

Query as follows:

UPDATE YourTableNameHere SET newValues = (DSum("CBC","YourTableNameHere ","ID between " & [ID] & " and " & ([id]-3)))/4;

Works for me.

http://www22.brinkster.com/accessory/

 

[dharkangel]

VERY COOL!! This exactly what i wanted!!!