cumulative rainfall query

[wilkesgra]

Hello,



I have a query building a table named (idpap rus running rain), with columns id, yrdy, and ru_totalrain, please see the attachment.

Can someone suggest an faster query to calculate cumulative rainfall in 1, 2, 3, and 7 days in advance (or records in advance) of each yrdy, including the value on yrdy?

I've been using a select query with dlookups like the following and it takes forever...

RU_TOTALRAIN1D: DLookUp("[RU_TOTALRAIN]","idpap rus running rain","[ID]=" & [ID]-1)+[RU_TOTALRAIN]

Especially for the 7 day cumulative lookup...

Suggestions would be appreciated!

[weekend00]

This query calculates 7 day advance (total 8 days), if you want 1,2,3,4 days, just change 7 to 1,2,3,4:

SELECT Sum(A.U_TOTALRAIN) AS SumOfU_TOTALRAIN, B.YRDY
FROM [idpap rus running rain] AS A, [idpap rus running rain] AS B
WHERE (((A.YRDY)>=[B].[YRDY] And (A.YRDY)<=[B].[YRDY]+7))
GROUP BY B.YRDY;

[wilkesgra]

Thanks for the reply. Sorry, to clarify, I meant values previous to the record, not after the record. To change, can I use -7???

[wilkesgra]

Oh, and not summing if there is lack of data in the moving window would be incredible.

[ajetrumpet]

wilk,

make sure to check your cross postings: http://www.access-programmers.co.uk/...d.php?t=199543

someone else provided you with a link that might just solve the problem right off...

if it doesn't though, that's fine, but cross posting is so frowned upon. There would be a lot of people that would appreciate it if you didn't do it. thanks!

[weekend00]

if you want to try again, change to following to calculate days previous:

WHERE (((A.YRDY)<=[B].[YRDY] And (A.YRDY)>=[B].[YRDY]-7))