Running Total Problem 1

[topdesk123]

Hello all!

I have a query that contains a running total for employee hours (RunTot: (DSum("acttime","time card hours","[TimeCardDetailID]<=" & [TimeCardDetailID] & "")). The problem is, it continues to run the total for every employee - instead of for EACH employee. For example, John Smith worked a total of 45 hours - Jack Schmidt worked a total of 30 hours - but the running total shows that Jack worked 75 hours instead of 30.

My tables are: Time Cards and Time Card Details. Time Cards includes EmployeeID; TimeCardID; and DateEntered.
Time Card Details includes: TimeCardID; TimeCardDetailsID; DateWorked; StartTime; EndTime; ActTime; Phase; ProjectID; Amount (the hourly amount paid for that particular job).

Can anyone help me please?

Thank you in advance.
topdesk123

 

[Trendsetter]

You need to apply "Grouping" on the Employee ID

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[topdesk123]

Hi Frank,

Thank you for your reply. I do have the Employee ID grouped...any other thoughts?

topdesk123

 

[PaulBricker]

What is your third argument pointing at. First, you have it set to <= so you are including any TimeCareDetailID that is less than the current in your totals. Second, what is
& [TimeCareDetailID] &
point to? Is it the field in the query pointing right back at the field in the query? I'm not sure you can do that and still get a true running total. It might help to see all the SQL

Paul

 

[topdesk123]

Hi Paul,

Here's the SQL:

SELECT [Time Card Hours].TimeCardDetailID, [Time Card Hours].Employee, Sum([Time Card Hours].ActTime) AS SumOfActTime, (DSum("acttime","time card hours","[TimeCardDetailID]<=" & [TimeCardDetailID] & "")) AS RunTot
FROM [Time Card Hours]
GROUP BY [Time Card Hours].TimeCardDetailID, [Time Card Hours].Employee;

See anything?

Thanks!
Gina

 

[PaulBricker]

Try this and see how it goes.

SELECT [Time Card Hours].TimeCardDetailID, [Time Card Hours].Employee, Sum([Time Card Hours].ActTime) AS SumOfActTime, (DSum("acttime","time card hours","[TimeCardDetailID]<=" & [TimeCardDetailID] & "[b]And [Employee] = '" & [Employee] & "'"[/b])) AS RunTot
FROM [Time Card Hours]
GROUP BY [Time Card Hours].TimeCardDetailID, [Time Card Hours].Employee;

This still doesn't seem right to me somehow but give a try and see how it goes.

Paul

 

[topdesk123]

Again, appreciating your help But, I get the error: Data Type Mismatch in Criteria Expression. Employee is a number if that makes a difference.

Thanks!
Gina

 

[PaulBricker]

It does. Take out the single (') quote marks and try that.

Paul

 

[fneily]

May I suggest you look at:

http://support.microsoft.com/?kbid=205183

The title of the article is "How to create a Grouped Running Sum in a Query". KB article 205183

I've used it, and it's pretty straight foward.

 

[topdesk123]

Paul, it works wonderfully! THANK YOU SO MUCH!

fneily - I will look into the article - thank you for the reference!!