rolling 3 weeks

[gwog]

using access 97

table structure
sdate date
item text
histqty double
histfcst double

sdate is always a sunday date (thus 1 week seperates each record).

example data:
sdate item histqty histfcst
7/4/04 10001 500 300
7/11/04 10001 100 50
7/18/04 10001 700 200
7/25/04 10001 50 100
8/1/04 10001 900 850
7/4/04 10002 900 700
7/11/04 10002 600 150
7/18/04 10002 300 500
7/25/04 10002 150 300
8/1/04 10002 400 250


I need to creating a rolling 3 week sum of histqty field and a rolling 3 week sum of histfcst field.

example:
sdate item 3weekhistqty 3weekhistfcst
7/18/04 10001 1300 550
7/25/04 10001 850 350
8/1/04 10001 1650 1150

7/18/04 10002 1800 1350
7/25/04 10002 1050 950
8/1/04 10002 850 1050

I tried to do it using a querie with the table 3 times (with alias names) and criteria of 2ndalias!sdate=1stalias!sdate+7 and 3rdalias!sdate=1stalias!sdate+14

But that did not give desired results.

Anyone know how I can accomplish this?

Thanks so much!

 

[Golom]

If the record that you are working with is (for example) the 7/25/04 record in 1stalias then you want the two previous records. Your selection criteria add to the date so you will get the two records after that one, not the two before it. Change your plus signs to minus signs and try it.