Attend system

[ashraf_al_ani]

Dear All
I have 2 tables one called checkinout which records the date and time of attend of an employee from the device the second table is called userinfo which contains the employee information such as the start shift time
I have 2 employee A and B
A his shift starts at 10:00:00 AM
B his shift starts at 11:00:00 Am

I want to make an update query that if A nad B enters earlier than their shifts start
his attend in checkinout table will be updated to the shift time and if they entered after that no changes will happen
Please any help

Best Regards

[ranman256]

I would add a field to tCheckINOut,
[ClockAdj] date/time (this is the adjusted time of the user who clocked in at 9am but wasnt scheduled until 10am, so this field would show adjusted time of 9am)
and
[ClockDirection] text 1 (I or O clock in our out)

sort the table by user/date, then mark the records I or O (1st records is a clock in, second is a clock out)

Adjust the users hours by the query below, to chop off time out of the worktime:

qsWorkTime = SELECT CHECKINOUT.USERID, CHECKINOUT.CHECKTIME, CHECKINOUT.THE_DATE, Format([CHECKTIME],"Medium Time") AS TimeMark, CHECKINOUT.ClockAdjFROM CHECKINOUT
ORDER BY CHECKINOUT.USERID, Format([CHECKTIME],"Medium Time");

qsWorktimeVsSchedule:
SELECT qsWorkTime.*, USERINFO.START_WORK, USERINFO.END_WORK
FROM USERINFO INNER JOIN qsWorkTime ON USERINFO.USERID = qsWorkTime.USERID;

with this query you can calculate the ClockAdj trimming.

Once you have clockIN/OUT then you can calculate hrs worked