Running sum

[dimkos]

Hi,

I have a time line with fixed intervals [Time_interval] and Events the task is to generate a query that will have a new field [Resourse] in a way that after each event we get in the next 2 consecutive intervals 1 resource.

Time_interval	Event	Resourse
2	yes	1
3		1
4
5
6	yes	1
7	yes	2
8		1
9
10	yes	1
11		1
12
13

thanks in advance

[June7]

What you want is not easy in query even when the data is suitable, and your data is not. The Event data does not lend itself to 'grouping' records - there are only 2 values - Yes or No. And what you want depends on data that can 'group' the records - an indicator that the group has changed and the numbering should start over.

Review: http://allenbrowne.com/ranking.html

[dimkos]

Isn't it possible to use somehow DSUM fuction in the [Resourse] field in a way that in the 'criteria' part i fix somehow 2 consecutive intervals ?

[June7]

Criteria must be based on data.

[InsuranceGuy]

Isn't it possible to use somehow DSUM fuction in the [Resourse] field in a way that in the 'criteria' part i fix somehow 2 consecutive intervals ?

Hi Dimkos,

There are many ways to do this, but a deeper understanding of your data structure and record recording standards are necessary in order to assist.

What is your source table structure? Are records reliably present for every interval? Are there any criteria other than "consider the previous two intervals", such as "consider the previous two intervals only when both intervals have event=yes"?

Can you provide sample data for your source table and a mock up of what you want the output to be?

If you invest a bit more, I'm sure we can help more.

Cheers,

Jeff