How can I create a Running Sum in a query that resets?

[Guest]

HELP! I am going crazy trying to solve this one. I have a query, lets call
it Query1 with the following three fields, Id (an ID#), Run (a Run#) and
Result (a test result that is either a 0 or 1). I need help creating the
programming to create the fourth field, the RunningSum that calculates a
cumulative total for each ID. Here is what the query presently looks like
with the first three fields (sorted ascending by ID, then Run). I cannot
create the fourth field:

Id Run Result RunningSum
1001 1 1 1
1001 2 0 1
1001 3 1 2
1001 4 1 3
1001 5 0 3
1002 1 0 0
1002 2 1 1
1002 3 0 1
1002 4 1 2
1003 1 1 1
1003 2 0 1

Please help...Thanks

 

[Guest]

Try something like

Select TableName.*, DSum("[Resault]","[TableName]","Id=" & [Id] & " And
Run<=" & [Run]) As RunningSum From TableName

If any of the fields (Id , Run) is a text field you need to add a single
quote before and after the string
For example:
Select TableName.*, DSum("[Resault]","[TableName]","Id='" & [Id] & "' And
Run<=" & [Run]) As RunningSum From TableName

 

[Guest]

GREAT! Thank you for your help, I really appreciate it!

Try something like

Select TableName.*, DSum("[Resault]","[TableName]","Id=" & [Id] & " And
Run<=" & [Run]) As RunningSum From TableName

If any of the fields (Id , Run) is a text field you need to add a single
quote before and after the string
For example:
Select TableName.*, DSum("[Resault]","[TableName]","Id='" & [Id] & "' And
Run<=" & [Run]) As RunningSum From TableName

--
Good Luck
BS"D

HELP! I am going crazy trying to solve this one. I have a query, lets call
it Query1 with the following three fields, Id (an ID#), Run (a Run#) and
Result (a test result that is either a 0 or 1). I need help creating the
programming to create the fourth field, the RunningSum that calculates a
cumulative total for each ID. Here is what the query presently looks like
with the first three fields (sorted ascending by ID, then Run). I cannot
create the fourth field:

Id Run Result RunningSum
1001 1 1 1
1001 2 0 1
1001 3 1 2
1001 4 1 3
1001 5 0 3
1002 1 0 0
1002 2 1 1
1002 3 0 1
1002 4 1 2
1003 1 1 1
1003 2 0 1

Please help...Thanks

 

[Guest]

Mike,

Another option would be:

SELECT T1.ID, T1.Run, T1.Result, SUM(T2.Result)
FROM yourTable T1 INNER JOIN yourTable T2
ON T1.ID = T2.ID
WHERE T2.Run <= T1.Run

You might want to try both to determine which is faster.

HTH
Dale