My data has fields that hold a code for a time period. For instance, one time period might be '2006010' and that refers to a time period in 2006. These time periods sort as text correctly (so 20060010 is always before 20060011), but numbers may be skipped (so there may be 20060012 and 20060014 but no 20060013). I didn't make this database, so I have to use that data as-is.
What I want to do is get the sum of all the data (data is a separate field) from the previous 4 periods for each period.
For example if I have:
Expand|Select|Wrap|Line Numbers
- 20060010 1
- 20060011 2
- 20060012 4
- 20060014 8
- 20060015 16
- 20060016 32
- 20060017 64
Expand|Select|Wrap|Line Numbers
- 20060010 0 (or NULL)
- 20060011 1
- 20060012 3
- 20060014 7
- 20060015 15
- 20060016 31
- 20060017 62 (notice that it does not add the 1, it only sums 2,4,8,16,32)
I am able to do this in another database with the following query:
Expand|Select|Wrap|Line Numbers
- SELECT DISTINCT
- i AS i1,
- (SELECT
- SUM(j)
- FROM
- (SELECT DISTINCT
- i,
- j
- FROM
- t
- WHERE
- i < t1.i
- ORDER BY
- i DESC
- LIMIT 5
- ) t2
- ) AS i2 FROM
- t AS t1;
Any help is appreciated. Also, if someone has a good link to an Access 2000 reference that would help me; for some reason I am having problems finding out which functions are even available to me to make my queries.
Thanks!
Jeff