Add clock values to report query

[Technics]

I have created a query and a report form for the query. Both are working fine giving me the results I have right now. When an employee clocks in and out I have a column that list their total time worked. An employee clocks in and out basically two times a day listing two sets of hours worked for each employee. What I would like to do is have an additional column that totals the two sets into one per employee. See photo. I have tried various calculations and some vb but have not been able to come up with anything that works or doesn't give me an error. Please give any and all suggestions. I hope I have explained this well enough. Sometime that is a problem within itself.

 

[June7]

Build aggregate query that calculates total MinutesWorked grouped by employee and date. Then do HoursWorked calc in a textbox on report.

SELECT TCtable.EmployeeID, EmpTable.EmployeeName, DateValue([TimeIn]) AS WorkDate, Sum(DateDiff("n",[TimeIn],[TimeOut])) AS MinutesWorked
FROM TCtable INNER JOIN EmpTable ON TCtable.EmployeeID = EmpTable.EmployeeID
GROUP BY TCtable.EmployeeID, EmpTable.EmployeeName, DateValue([TimeIn]);

If you want to show detail records, use report Sorting & Grouping and aggregate calcs in textboxes.

Whichever, this will get more complicated if work period crosses midnight.

Note for future posting: best to provide SQL statement of query. Image often does not show complete query, such as calculations.

 

[DHookom]

Typically you would group your report by employee and date. Add a header or footer for this grouping to display the sum of the time worked.

As June7 suggested, there could be issues if shifts span midnight.

 

[LarryE]

That's a form, not a report, so you can't do groupings by employee, but you can calculate the totals you want with a textbox control in the form and using the DSum() function with the EmployeeID as criteria:
=DSum("HoursWorked","Your table name","[EmployeeID]=" & [EmployeeID)

 

[DHookom]

That's a form, not a report, so you can't do groupings by employee, but you can calculate the totals you want with a textbox control in the form and using the DSum() function with the EmployeeID as criteria:
=DSum("HoursWorked","Your table name","[EmployeeID]=" & [EmployeeID)

Are you suggesting you can’t use a report grouping to calculate aggregates or are you referencing @June7 post?

You can’t use an aggregate function in a header or footer based on the name of a control in the detail section but you can use fields from the report’s recordsource.

 

[Ken Sheridan]

The attached file illustrates the use of a number of time arithmetic functions, whose return values are in conventional time format.

If you open the form for the summation of date/time values you'll see that it returns the time worked in each row. At present there are two rows per day as in your case, but the functions can easily return the time worked per employee per day, as in the following simple query:

SELECT
    EmployeeID,
    WorkDate,
    TimeElapsed (SUM(TimeDurationAsDate (TimeStart, TimeEnd)),"nn") AS DailyTime
FROM
    TimeLog
GROUP BY
    EmployeeID,
    WorkDate;

 

[Gasman]

That's a form, not a report, so you can't do groupings by employee, but you can calculate the totals you want with a textbox control in the form and using the DSum() function with the EmployeeID as criteria:
=DSum("HoursWorked","Your table name","[EmployeeID]=" & [EmployeeID)

Some date criteria would be needed as well, I suspect?

 

[LarryE]

Are you suggesting you can’t use a report grouping to calculate aggregates or are you referencing @June7 post?

You can’t use an aggregate function in a header or footer based on the name of a control in the detail section but you can use fields from the report’s recordsource.

I am suggesting the image the OP presented is a form and not a report, so you cannot group records like a report can. The OP asked a question about how to group records in a report but presented a form, I may have misunderstood what they were asking, but it appears they want to calculate total hours per employee on the form they presented.

 

[Technics]

I have created a query and a report form for the query.

I apologize for the confusion. It is a form for my query and not a report. I just call it my "report" form.

 

[Technics]

I hope this helps. As you can see in the query I am using this set of calcs to get my Minutes Worked and Hours Worked.

MinutesWorked: DateDiff("n",[TimeIn],[TimeOut])
HoursWorked: Round([MinutesWorked]/60,2)
WholeHoursWorked: Int([hoursworked])
MinutesLeft: [MinutesWorked]-([WholeHoursWorked]*60)
Duration: [WholeHoursWorked] & ":" & Format([MinutesLeft],"00")

I was wondering if there is another calc I can use in addition to these to get my total hours worked per employee or something else that will give me the totals. The answer may be above but it has been many years since I have tried to use Access.

 

[June7]

Oops, I overlooked the word "form" in original post. If you don't want form to be used for data entry/edit, then build aggregate query as I suggested and use that as RecordSource.

If you want to be able to do data entry/edit, then, as Gasman suggested, use domain aggregate expression in textbox. Do MinutesWorked and date part extract calcs in query. Save SQL as a query object so it can be used as RecordSource and referenced by domain aggregate function.

=DSum("MinutesWorked","QueryName","EmployeeID=" & [EmployeeID] & " AND DateWorked=#" & [DateWorked] & "#")

Then do HoursWorked calc in another textbox that references aggregate calc textbox.
=[tbxMinutes] \ 60 & Format([tbxMinutes] Mod 60, "\:00")

Or try custom function as suggested by Ken, which should be able to call from query or textbox.

 

[DHookom]

I am suggesting the image the OP presented is a form and not a report, so you cannot group records like a report can. The OP asked a question about how to group records in a report but presented a form, I may have misunderstood what they were asking, but it appears they want to calculate total hours per employee on the form they presented.

You are a better mind reader than I am

 

[Technics]

As I said prior, it has been years, 25, since I have messed with Access but I am a quick learner especially if I have worked with it before. This is my first project back and I have tried to work with all these great solutions, greatly appreciated I might add, but right now I am in the "Do I have to spell it out for you!" phase. Could anyone help a little on how to implement these solutions. Do you need to see more of my forms or tables. I am creating a Report for the form that will show what the form shows.

 

[LarryE]

I am creating a Report for the form that will show what the form shows.

OK then create a report based upon the same query data you used on the form. Use that query as the reports record source. Then you can group the report by EmployeeID, and add a group footer for the EmployeeID. Then you can add totals for each employee.

 

[June7]

Do you want to show detail records or just aggregate so one line per employee per day?

Either way can be accomplished with report Sorting & Grouping and aggregate calcs in textboxes.

Or use aggregate query as suggested.

I think what has been provided pretty much "spells it out" so you need to tell us exactly what issue you encountered - error message, wrong result, nothing happens?

Could provide your db for analysis. Follow instructions at bottom of my post.

 

[Ken Sheridan]

I was wondering if there is another calc I can use in addition to these to get my total hours worked per employee or something else that will give me the totals.

Taking the form in my TimeArithmetic demo as an example, a text box with the following expression as its ControlSource property could be added to the form's Detail section to return the total time worked by each employee per day:

=DLookUp("DailyTime","qryDailyTimeWorked","EmployeeID = " & Nz([EmployeeID],0) & " And WorkDate = #" & Format(Nz([WorkDate],0),"yyyy-mm-dd") & "#")

where qryDailyTimeWorked is the name of the query I posted in my earlier reply, viz:

SELECT
    EmployeeID,
    WorkDate,
    TimeElapsed (SUM(TimeDurationAsDate (TimeStart, TimeEnd)),"nn") AS DailyTime
FROM
    TimeLog
GROUP BY
    EmployeeID,
    WorkDate;

 

[gemma-the-husky]

@Technics

You don't do a night shift do you?

If you have employees clocking on at 6pm say, and finishing at 2pm, you now have different days for each time stamp, and this will need to be taken into account.

 

[Ken Sheridan]

If you have employees clocking on at 6pm say, and finishing at 2pm, you now have different days for each time stamp, and this will need to be taken into account.

The functions in my TimeArithmetic demo attached to an earlier reply cater for this as below:

Public Function TimeDuration(dtmFrom As Date, dtmTo As Date, _
            Optional blnShowdays As Boolean = False) As String
            
    ' Returns duration between two date/time values
    ' in format hh:nn:ss, or d:hh:nn:ss if optional
    ' blnShowDays argument is True.
    
    ' If 'time values' only passed into function and
    ' 'from' time is later than or equal to 'to' time, assumed that
    ' this relates to a 'shift' spanning midnight and one day
    ' is therefore subtracted from 'from' time

    Dim dtmTime As Date
    Dim lngDays As Long
    Dim strDays As String
    Dim strHours As String
    
    ' subtract one day from 'from' time if later than or same as 'to' time
    If dtmTo <= dtmFrom Then
        If Int(dtmFrom) + Int(dtmTo) = 0 Then
            dtmFrom = dtmFrom - 1
        End If
    End If
    
    ' get duration as date time data type
    dtmTime = dtmTo - dtmFrom
    
    ' get whole days
    lngDays = Int(dtmTime)
    strDays = CStr(lngDays)
    ' get hours
    strHours = Format(dtmTime, "hh")
    
    If blnShowdays Then
        TimeDuration = lngDays & ":" & strHours & Format(dtmTime, ":nn:ss")
    Else
        TimeDuration = Format((Val(strDays) * 24) + Val(strHours), "#,##0") & _
            Format(dtmTime, ":nn:ss")
    End If
    
End Function