# Thread: Need help with running total in query

1. Registered User
Join Date
Oct 2009
Posts
204

## Unanswered: Need help with running total in query

Hello,

I am having difficulty having a running total calculation work the way I need it to. I am trying to total hours per day per employee based on each punch out. Here is what I currently have:

Source query - qry_Test
Fields in query - UniqueFld, DateFld, TimeOut, EmployeeName, Hours

Here is the query that is giving me a running total:

Field 1
RT: DSum("[Hours]","[qry_Test2]","[UniqueFld]<='" & [qry_Test2].[UniqueFld] & "'")
Field 2
DateFld
Field 3
EmployeeName
Field 4
TimeOut
Field 5
Hours

This gives me a running total of the Hours field for every line. The result looks like this:
RT | DateFld | EmployeeName | TimeOut | Hours
0.1 | 03/28/2011 | Employee 1 | 14.58 | 0.10
8.23 | 03/28/2011 | Employee1 | 20.14 | 8.13
8.53 | 03/28/2011 | Employee1 | 20.32 | 0.30
16.1 | 03/29/2011 | Employee1 | 19.34 | 7.57

What I need is for the running total to start over when the criteria of DateFld or EmployeeName changes. Here is an example of what I need:
RT | DateFld | EmployeeName | TimeOut | Hours
0.1 | 03/28/2011 | Employee 1 | 14.58 | 0.10
8.23 | 03/28/2011 | Employee1 | 20.14 | 8.13
8.53 | 03/28/2011 | Employee1 | 20.32 | 0.30
7.57 | 03/29/2011 | Employee1 | 19.34 | 7.57

I am now officially stumped. Does anyone know what my next step should be?

2. Registered User
Join Date
Oct 2009
Posts
204
Ok I got it to break down by EmployeeName with this modified dsum:

RT: DSum("[Hours]","[qry_RunningTotal1]","EmployeeName='" & [EmployeeName] & "'And [UniqueFld]<='" & [qry_RunningTotal1].[UniqueFld] & "'")

For some reason I get 04534095 errors when I try to add a third criteria in for DateFld. The workaround I will be using for the time being is controls in the form that loads the query that will prevent loading more than one week's worth of data at one time. Does anyone have a solution to this issue?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•