Add clock values to report query (1 Viewer)

Technics

New member
Local time
Today, 15:40
Joined
Aug 9, 2025
Messages
6
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.
accessworld.jpg
 
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.
 
Last edited:
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.
 
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)
 
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.
 
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:

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

Attachments

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?
 
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.
 
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.
 
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.
 
Last edited:
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 😊
 
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.
 
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.
 

Users who are viewing this thread

Back
Top Bottom