Solved Attendance report based on ideal timeframe table for clcok-in information. SQL queries.

[devtr]

Three tables for the clock-in application at the hotel.

emp_clock has employees clock in and clock out information.

dept_mast is the master department with the primary key. It has departments like Breakfast, Housekeeping, Laundry, etc.

dept_shift_times has all departments and their ideal clock in time timeframe.
For example, for breakfast department employees, for Mon to Fri, it is from 5:00 am to 5:30 am.
And for Sat and Sun, it is from 6:00 am to 6:30 am.

All times are in short time format with 24 hr time.

For the attendance report of the employees, I wrote a query below, that shows On-time or Late based on their clock-in time.
If their clock-in time falls between their designated timeframe, it's On time, if they clocked in late it's Late.

SELECT emp_clock.emp_no, emp_clock.emp_dept, emp_clock.clin_date, emp_clock.clin_time, emp_clock.clout_date, emp_clock.clout_time, emp_clock.worked_min, Format([clin_date],"ddd") AS Workday,

IIf(

(Format([clin_date],"ddd") In ("Mon","Tue","Wed","Thu","Fri")) And (emp_clock.clin_time<=format(#5:30:0#, "Short time")),"On time",

iif((Format([clin_date],"ddd") In ("Sat","Sun")) And (emp_clock.clin_time<=format(#6:30:0#, "Short time")),"On time", "Late"

)) AS Attendance

FROM emp_clock
WHERE ((emp_clock.emp_dept)="Breakfast");

How do I use the dept_shift_times table with this?
I do not want to include the time directly in the query also.
At this time, I can only think of Union queries for each department with their shifts. And then combine all queries to come up with the result with all the departments.
Again, I will have to find the timeframe for each department (and their shifts and days) and use that in the query.

Please find the small demo file attached. It was created in Access 2019.
Thank you everyone in advance for your time and for sharing the knowledge.

 

[pbaldy]

It seems like there's a missing piece of info in your times table, the shift type. Otherwise you're making an assumption about which shift I'm clocking in/out for. At 8am am I late for the morning shift or early for the afternoon shift?

Also, your shift times table isn't what's called "normalized". You have multiple pieces of information in a single field, days of the week. I'd have a table where there's a record for each day of the week. That would allow you to join to this table rather than have some sort of complicated and inefficient lookup. I'd also use a number for the day of the week, so that using the Weekday function on the date will allow you to join to this table.

 

[devtr]

SELECT emp_clock.emp_no, emp_clock.emp_dept, emp_clock.clin_date, emp_clock.clin_time, dept_shift_times.work_days.Value, dept_shift_times.shift_type,
IIf((Format([emp_clock].[clin_time],"Short Time")<=Format([dept_shift_times].[clin_to_time],"Short Time")),"On time","Late") AS Attendance, dept_shift_times.shift_start_time, dept_shift_times.shift_end_time, dept_shift_times.clin_from_time, dept_shift_times.clin_to_time, emp_clock.clout_date, emp_clock.clout_time, emp_clock.worked_min, dept_shift_times.dept, dept_shift_times.work_days
FROM dept_shift_times INNER JOIN emp_clock ON dept_shift_times.dept = emp_clock.emp_dept
WHERE
(((Format([emp_clock].[clin_date],"ddd"))=[dept_shift_times].[work_days].[Value]) AND ((Format([emp_clock].[clin_time],"Short Time"))<=Format([dept_shift_times].[shift_end_time],"Short Time") And (Format([emp_clock].[clin_time],"Short Time"))>=Format([dept_shift_times].[shift_start_time],"Short Time")))
ORDER BY emp_clock.clin_date, emp_clock.clin_time;

I added two more columns in dept_shift_times with shift start time and shift end time. That will recognize what shift the employee worked in the times' table emp_clock.

pbaldy's comments gave me this idea. Instead of adding columns in times table, I used the dept table.
Thank you.

 

[pbaldy]

I'm glad you found a solution.