Results 1 to 2 of 2
  1. #1
    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. #2
    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
  •