DSUM function for running total (1 Viewer)

[canonquiche]

I want to add up the current hours for each position by month to create a running total ("Running Hours)". This running total should also reset from one contract year to the next.

The result should look like this:

[FONT="Courier New"]Contract Year	Invoice Month	Labor Cat Desc	      Current Hours	Running Hours
OY1	          01/01/2015	Help Desk Specialist	     10	          10
OY1	          02/01/2015	Help Desk Specialist	     10	          20
OY1	          03/01/2015	Help Desk Specialist	     10	          30
OY1	          04/01/2015	Help Desk Specialist	     10	          40
OY1	          05/01/2015	Help Desk Specialist	     10	          50
OY1	          01/01/2015	Program Manager	              5	          5
OY1	          02/01/2015	Program Manager	              5	          10
OY1	          03/01/2015	Program Manager		      5	          15
OY1	          04/01/2015	Program Manager	              5	          20
OY1	          05/01/2015	Program Manager		      5	          25
OY1	          01/01/2015	Test Engineer		      4	          4
OY1	          02/01/2015	Test Engineer		      4	          8
OY1	          03/01/2015	Test Engineer		      4	          12
OY1	          04/01/2015	Test Engineer		      4	          16
OY1	          05/01/2015	Test Engineer		      4	          20
OY2	          06/01/2015	Help Desk Specialist	      10	  10
OY2	          07/01/2015	Help Desk Specialist	      10	  20
OY2	          08/01/2015	Help Desk Specialist	      10	  30
OY2	          06/01/2015	Program Manager	              5	          5
OY2	          07/01/2015	Program Manager	              5	          10
OY2	          08/01/2015	Program Manager	              5	          15
OY2	          07/01/2015	Test Engineer		       4	   4
OY2	          08/01/2015	Test Engineer		       4	   8[/FONT]

I tried the query below, but it returns blank rows for Running Hours. What am I missing?

Running Hours: DSum("[Current Hours]","ITISS Summary","[Labor Cat Desc]= ' " & [Labor Cat Desc] & " ' AND [Contract Year]= ' " & [Contract Year] & " ' ")

 

[pbaldy]

Try without the spaces:

Running Hours: DSum("[Current Hours]","ITISS Summary","[Labor Cat Desc]= '" & [Labor Cat Desc] & "' AND [Contract Year]= '" & [Contract Year] & "'")

 

[canonquiche]

I should probably mention that "Contract Year" itself is an expression within the same query. Is that a problem?

When I try to run the updated code suggested by pbaldy, I get:

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Contract Year."

When I turn on the totals and change the calculated field to an "Expression" I get:

Your query does not include the specified expression [blah blah] as part of an aggregate function.

 

[pbaldy]

You probably need to repeat the calculation rather than use the alias.

 

[canonquiche]

You probably need to repeat the calculation rather than use the alias.

That was it -- thank you.

Incidentally, the code needed the Invoice Month field as well:

Running Hours: DSum("[Current Hours]","ITISS_Summary","[Labor Cat Desc]= '" & [Labor Cat Desc] & "' AND [B][Invoice Month]<=#" & [Invoice Month][/B] & "# AND [ContractYear]= '" & [ContractYear] & "'")

 

[pbaldy]

Happy to help!