DSum and Filters

[slybus]

Hi, I have several projects with different tasks for each. I have 3 fields [fkProjectsID], [TaskOrder] and [Duration] in a table for project tracking with that structure:

[fkProjectsID] [TaskOrder] [Duration]
1 /1 /5
1 /2 /8
1 /3 /15
1 /4 /6
2 /1 /8
2 /2 /30
2 /3 /25

I want to calculate cumulative values stored in [Duration] field (represent a number of days). I'm using the field [TaskOrder] to order different tasks within each project. With some testing, I was able to calculate cumulative [Duration] with 1 project using the DSum fucntion as following:

CumulDuration: DSum("[Duration]", "[tblProjectTracking]", "[TaskOrder]<=" & [TaskOrder])

I was having the sequence: 5, 13, 28, 34 for respectively Task 1,2,3,4. However, when I add a second project (and then a third...), I need to be able to filter based on [fkProjectsID] as well (i.e. a specific DSum by ProjectsID). I add this criteria but I get the sum of [Duration] on each row instead of the cumulative:

CumulDuration: DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] And "[fkProjectsID]=" & [fkProjectsID])

Thanks for the help.

 

[madefemere]

Hello,

Like this ?

SELECT MyTable.fkProjectsID, MyTable.TaskOrder, MyTable.Duration, DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] & " And [fkProjectsID]=" & [fkProjectsID]) AS Cumul
FROM MyTable
ORDER BY MyTable.fkProjectsID, MyTable.TaskOrder;

 

[slybus]

Wow! It worked. I was so close. Do you have a link or can you explain when "&" are required?

Thanks for the help!

 

[madefemere]

DSum("[Duration]","[tblProjectTracking]","[TaskOrder]<=" & [TaskOrder] & " And [fkProjectsID]=" & [fkProjectsID])

- The first [TaskOrder] refers to the field of the table [tblProjectTracking].
- The second is the current value of the record. In an EXPRESSION, you must write it between "&".

So if you have below values, the expression of criteria will be the next :
fkProjectsID TaskOrder criteria
1 1 "[TaskOrder]<=1 And [fkProjectsID]=1"
1 2 "[TaskOrder]<=2 And [fkProjectsID]=1"