Problem with Running Total

[JoeyB_99]

I am using Access 2007 and having trouble getting a running total in a query.

Here is the basic data coming from a table called tblLabourHours2

ID (autonumber generated)
Formatted Cost Code
TransDate
Hours

My goal is to get a running total of hours for each cost code. A cost code may have hours for several different dates.

I need to know the running total for each date within a cost code since I may run reports/queries based on a date range.



The columns of my query has ID, Formatted Cost Code, TransDate, Hours, and the following expression in the next column

Expr1: DSum("Total Hours","tblLabourHours2","Formatted Cost Code = " & [Formatted Cost Code] & " And ID <=" & [ID])


I get #ERROR in the expression column.

What am I doing wrong?

 

[nathansav]

SELECT tblLabourHours2.Trans_Date, tblLabourHours2.Cost_code, Sum(tblLabourHours2.Hours) AS SumOfHours
FROM tblLabourHours2
GROUP BY tblLabourHours2.Trans_Date, tblLabourHours2.Cost_code;

 

[JoeyB_99]

Thank you Nathansav, but it did not work.

Your solution does not provide a running total. It merely provides the sum of hours per record.

Also, if I remove the Group By I get the same results.

Any other suggestions would be appreciated.

 

[plog]

Can you provide some sample datafrom tblLabourHours2 and then what you want the result to be from this query based on that sample data?

 

[JoeyB_99]

Okay plog....here goes.

sample table data
1, LAB.123, 10/13/2012, 55.0
2, LAB.123, 10/20/2102, 2.5
3, LAB.123, 10/27/2012, 15.0
4, LAB.129, 10/27/2012, 99.0
5, LAB.155, 10/13/2012, 45.0
6, LAB.155, 10/31/2012, 55.0

The desired output should look like this,

LAB.123, 10/13/2012, 55.0
LAB.123, 10/20/2012, 57.5
LAB.123, 10/27/2012, 72.5
LAB.129, 10/27/2012, 99.0
LAB.155, 10/13/2012, 45.0
LAB.155, 10/31/2012, 100.0

 

[plog]

In your initial try, you did a few things wrong:

[FONT=Tahoma]Expr1: DSum("Total Hours","tblLabourHours2","Formatted Cost Code = " & [Formatted Cost Code] & " And ID <=" & [ID])[/FONT]

1. The biggest sin was using field names with non-alpha and non-numeric characters ("Total Hours" and "Formatted Cost Code"). When you have special characters in your field names, you need to bracket your fields: [Total Hours] and [Formatted Cost Code] when using them in an expression.


2. You also treated text data like numeric data when you tried to compare them ("Formatted Cost Code = " & [Formatted Cost Code]). When comparing text you need to put quotes around it.


3. You were right in thinking you needed 2 criteria in your DSUM function, but you need to use the date, not the ID. The ID was unique so you would only return one row. You needed to use the date field.

This is what the expression should look like using the fields you have in your table:

RunningTotal: DSum("[Total Hours]","tblLabourHours2","[Formatted Cost Code]='" & [Formatted Cost Code] & "' AND [TransDate]<=#" & [TransDate] & "#")

 

[JoeyB_99]

Okay then, thank you plog.


I updated my expression to the one you supplied, including the proper placement of square brackets, and I now get the following error message,


Syntax error (missing operator) in query expression 'Formatted Cost Code = 'LAB.123' AND TransDate <= #10/13/2012#'.


What is missing? Any help would be greatly appreciated.

 

[toast]

Are you sure you put all the square brackets in?

That looks like the result of

RunningTotal: DSum("[Total Hours]","tblLabourHours2","Formatted Cost Code='" & [Formatted Cost Code] & "' AND TransDate<=#" & [TransDate] & "#")

rather than (differences shown in red)

RunningTotal: DSum("[Total Hours]","tblLabourHours2","[color=red][[/color]Formatted Cost Code[color=red]][/color]='" & [Formatted Cost Code] & "' AND [color=red][[/color]TransDate[color=red]][/color]<=#" & [TransDate] & "#")

(although the lack of square brackets around TransDate isn't crucial)

 

[JoeyB_99]

Mission accomplished! Eureka! It now works.

All I did was add in the square (red) brackets as noted above by toast.

Thank you one and all - nathansav, plog, and toast. I appreciate the volunteering of your time and the parting of your knowledge.

 

[lucy6868]

Toast, I sure hope you are still around. Here is my query:

RT2: DSum("[DemandQty]","[qryMRPDetail_PartShortages_Sub]","[UniqueRecord] <= " & [UniqueRecord] & " ")

It returns: #Error

Can you review my syntax?

Regards,

Lucy

 

[CJ_London]

this is a double post

http://www.access-programmers.co.uk/forums/showthread.php?t=264463