Running Totals in a Query 1

[skeletor]

I’ve searched everywhere and can’t figure out how to create a query that adds up employee overtime hours and keeps a running total. I found a Dsum example, that works great for a single employee, but adds all employee hours when multiple employees are queried.

The table I use is called overtime. The fields that are queried are date, employee id (a number field), hours, and a query created total field.

Can anyone please help.

 

[dhookom]

Is there any grouping by week? What did you try with DSum()?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya skeletor . . .

A detailed example along with table/field/form/control names of what your after would be a great help!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[skeletor]

Thank you for interest in my question.
The query uses the overtime table, and the fields are:
Emp# - number field
ShiftDate – date field, one for each day of the year
Hour – number field, represents overtime hours

SRC Total 1
Emp# ShiftDate Hour
514099 8/16/2010 0
514099 8/17/2010 0
514099 8/18/2010 0
514099 8/19/2010 0
514099 8/20/2010 12.25
514099 8/21/2010 0
514099 8/22/2010 0
514099 8/23/2010 0
514099 8/24/2010 0
514099 8/25/2010 0
514099 8/26/2010 0
514099 8/27/2010 0
514099 8/28/2010 0
514099 8/29/2010 0
514099 8/30/2010 0
514099 8/31/2010 0
514099 9/1/2010 12.25
514099 9/2/2010 0
514099 9/3/2010 0
514099 9/4/2010 0
514099 9/5/2010 0
514099 9/6/2010 0
514099 9/7/2010 0

I would like to create a query where the accumulated hours are calculated automatically as below.

SRC Total 1
Emp# ShiftDate Hour Total
514099 8/16/2010 0 0
514099 8/17/2010 0 0
514099 8/18/2010 0 0
514099 8/19/2010 0 0
514099 8/20/2010 12.25 12.25
514099 8/21/2010 0 12.25
514099 8/22/2010 0 12.25
514099 8/23/2010 0 12.25
514099 8/24/2010 0 12.25
514099 8/25/2010 0 12.25
514099 8/26/2010 0 12.25
514099 8/27/2010 0 12.25
514099 8/28/2010 0 12.25
514099 8/29/2010 0 12.25
514099 8/30/2010 0 12.25
514099 8/31/2010 0 12.25
514099 9/1/2010 12.25 24.5
514099 9/2/2010 0 24.5
514099 9/3/2010 0 24.5
514099 9/4/2010 0 24.5
514099 9/5/2010 0 24.5
514099 9/6/2010 0 24.5
514099 9/7/2010 0 24.5


The dsum() that I used, that didn’t work right, used the query SRC Total 1 for the data source and the code used was:

SELECT DatePart("yyyy",[shiftdate]) AS AYear, DatePart("m",[shiftdate]) AS AMonth, DatePart("d",[shiftdate]) AS ADay, DSum("hour","src total 1","DatePart('d', [shiftDate])<=" & [Aday] & " And DatePart('m', [shiftDate])<=" & [AMonth] & " And DatePart('yyyy', [shiftDate])<=" & [AYear] & "") AS HourTot, Format([shiftdate],"mmm") AS FDate
FROM [SRC Total 1]
WHERE (((DatePart("yyyy",[shiftdate]))=2011))
GROUP BY DatePart("yyyy",[shiftdate]), DatePart("m",[shiftdate]), DatePart("d",[shiftdate]), Format([shiftdate],"mmm")
ORDER BY DatePart("yyyy",[shiftdate]), DatePart("m",[shiftdate]), DatePart("d",[shiftdate]), Format([shiftdate],"mmm")
WITH OWNERACCESS OPTION;

Then I used the above query titled SRC Total 2 for a new query SRC Total 3:

SELECT T1.ShiftDate, T1.Hour, (SELECT Sum([src total 1].hour) AS Total
FROM [src total 1]
WHERE [src total 1].shiftdate <= T1.shiftdate) AS Total, T1.[Emp#]
FROM [src total 1] AS T1;

The above worked fine as long as it was filtered to one employee in SRC Total 1. As soon I removed the filter and included additional employees, it added everyone’s hours to come up with a cumulative total for all employees.

 

[TheAceMan1]

skeletor . . .

I see what you mean ... and you'll accomplish this with great diffculty. Its the nature of the running Sum that makes it tough. I don't see how this can be done in query ... at least not directly with more than one employee. Best I see is looping thru employee's, running the query on each, then appending to a table for viewing.

Since your only interested in total overtime, a simple [blue]Totals[/blue] query would provide the answers you seek for all (and would not be cluttered with redundency).

My question is:

[blue]Is there any reason why you need to see/view the [purple]redundancy[/purple] you've show?[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[dhookom]

I'm not sure why you would post sample data with one employee when clearly your primary issue is more than one employee in your recordset.

Try something like:

SELECT qselOverTime.EmpNum, qselOverTime.ShiftDate, qselOverTime.Hours, 
(SELECT Sum(Hours) FROM qselOverTime Q WHERE Q.EmpNum = qselOverTime.EmpNum AND q.ShiftDate <=qselOverTime.ShiftDate) AS AccumOTHours
FROM qselOverTime
ORDER BY qselOverTime.EmpNum, qselOverTime.ShiftDate;

Duane
Hook'D on Access
MS Access MVP

 

[skeletor]

Thanks to TheAceMan and dhookom for responding to my thread.

To dhookom, your solution worked great, thank you very much.