Update "In/Out" for employee time sheet. 1

[ongke0711]

Dear All,
I have a list of Employee time sheet from Fingerprint time attendance machine. But the machine software have problem so the time In and Out was not displayed on the Field IO so I cannot know what time is In, what time is Out.
The list as below.

Could you help me to use Query or VBA code to update "In" or "Out" to every pair of row with the same WorkedDate and EmployeeID. The 1st row is "IN", 2nd row is "OUT". If there's only row, update it with "IN".
Thank you very much.

Link .mdb file: Link

 

[Andrzejek]

I don't know if that's ALWAYS the case, but from your example it looks to me that IN is in the morning, and OUT is in the afternoon. So you may be able to do just:
[tt]
UPDATE MyTable
Set IO = 'In'
WHERE [Time] = before Noon
[/tt]
and then
[tt]
UPDATE MyTable
Set IO = 'Out'
WHERE IO IS NULL
[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

But the machine software have problem so the time In and Out was not displayed on the Field IO...

Are you sure that there's a problem? Could there be a non-printable code? You need to check.

You really can't 'assume' that AM times are in and PM times are out.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ongke0711]

Thank Andrzejek and SkipVought for your reply.
We cannot assume that AM is [IN] and PM is [OUT].
Ex: There's a 2nd Shift from 14:30 - 22:00 -> 14:30 is [IN] and 22:00 is [OUT]
So I cannot use Update query as Andrzejek suggested because cannot define what time is IN or OUT.
The arithmetic of Fingerprint machine is "record the 1st time of fingerprint is IN and the 2nd time is OUT" and repeat 3rd time is IN, 4th time is OUT. It don't care what is the time of fingerprint. Some case employee just take fingerprint 1 time and it will record as [IN].
So I need to use this arithmetic to update the time sheet list.

@SkipVought: the problem with machine because of wrong setting and still not yet fix (It's supplier gone ) . So I need to do by myself first to have data in "In/Out" field

 

[Andrzejek]

Do you have a shift where people can get IN one day and OUT the next day?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Some case employee just take fingerprint 1 time and it will record as [IN].

Please explain what this means.

Are you saying that on one day, say Monday, an employee will Fingerprint [IN] and there would be no other Fingerprint for Monday? Would the employee Fingerprint [OUT] on Tuesday, which might appear to be an [IN]?

There's a 2nd Shift from 14:30 - 22:00 -> 14:30 is [IN] and 22:00 is [OUT]

What if an employee on the 2nd shift works until 00:15 the next morning where 00:15 is [OUT]?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ongke0711]

Do you have a shift where people can get IN one day and OUT the next day?

Yes, we have.
1st Shift: 6:30 - 14:30
2nd Shift: 14:00 - 22:00
3rd Shift: 22:00 - 6:00

Please explain what this means.

Sometimes there's a case that Employee went home and forgot to fingerprint [OUT] so machine has just record 1 time of fingerprint for that employee. Record 1 time is [IN].

The case 2nd shift works until 0:15 the next morning -> 0:15 is [IN]

The next day which is started at 0:00 AM, the machine will restart and record with [IN] for 1st time fingerprint, [Out] for 2nd time in a day... (until 24:00).

To the case of 3rd Shift (OUT on next morning), we do manual to adjust the [IN] become [OUT] on the timesheet before import to the HR application.

 

[ongke0711]

Please try to help me this issue.
Thank you very much.

 

[SkipVought]

The reason that no one has posted a solution is that your system is broken. There is no logic that can be applied EXTERNALLY. It must be repaired INTERNALLY.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

...what Skip said.
Also, you can write a complicated logic to check if employee has only 2 entries in a given day, and if the difference between the earlier time and the later time is about 8 hours - that would suggest it is a regular shift. But what if the employee goes home earlier because he/she is sick? You would still need to do a lot of manual checks daily (?) And if this system is used to determine people's salaries based on their hours of work, you will have a lot of unhappy people because they will not get paid for the time they worked - potentially.



Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

What Andy's saying is, yes, you could guess and derive a definite maybe!

But even if you calculate two shifts in one day: SO WHAT! Which entry is IN? Looking from the outside, where you and I and all of us are, NO ONE CAN DETERMINE with 100% certainty!

Your company needs to institute a temporary emergency manual or automated solution apart from Fingerprint or to supplement Fingerprint. Your current time system is broken.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ongke0711]

Regard to put IN/OUT in the time sheet list, I just want to imitate what the fingerprint machine does. The machine has 3 options for setting to record employees in and out.
1. Record base on switching IN/OUT on menu of machine. Employees have to chose IN or OUT before fingerprint.
2. Record base on setting employee's shift. Input the shift by each employee on machine application.
3. Record by each pair of fingerprint. => this option I mentioned. The machine records automatically 1st time is In, 2nd is OUT and so on. I also can set the starting time of a day (ex: 0:00 AM or 5:00 AM is start a new day).

To the calculation employee working hour, we have a HR application to do this. The import time sheet from machine to application is just 1st step and 1 HR staff has to adjust this data base on the the sick leave, go out permission sheet, day off etc...=> to get the correct worked hour by employee. After that the salary calculation, bonus, penalty... will do.

Anyway thank much for your spending time to my issue.

 

[SkipVought]

If you cannot state to us the data conditions that will unambiguously determine what record parameters indicate IN and what record parameters indicate OUT, then NO ONE can help you.

Restating the issue gets no one anywhere.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ongke0711]

...unambiguously determine what record parameters indicate IN and what record parameters indicate OUT...

Maybe my English is not so good for the explanation.
As I said before, the machine do not need parameter to indicate what is IN what is OUT, It just bases on EmployeeCode (no need to set the Shift) and how many time he puts the finger into the machine to take fingerprint. He puts 1 time (the 1st time of the day - 0:00), it marks "IN", he puts 2nd time -> marks "OUT", 3rd time -> marks "IN"... The machine run automatically like this and just because the programer programed it work like that. So I would like to copy how they program it that way.

 

[SkipVought]

So I would like to copy how they program it that way.

Exactly what does that mean? Their program is probably an executable. Who knows if it was programmed in a proprietary language? It is a "black box." There's no way to determine "how they program it that way." All you can know what goes into the "black box" and what comes out.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

If for the same date you need a sequence of IN-OUT-IN-OUT-..., you can:
1) create a query that adds serial numbers SN to subsets with the same EmployeeID and WorkDate basing on Time, see this for idea and SQL,
2) create a query based on query (1) that converts odd SNs to "In", even SNs to "Out" (Iif([SN] Mod 2 = 1,"In","Out"))

combo

 

[Andrzejek]

Well, you have a situation where you have a record with the time of 6:15 (am)
Is it IN or is it OUT?
If I am coming for my first (day) shift, it is IN
If I am leaving the grave-yard shift - it is OUT

You may come up with the logic that will give you a 90% accuracy (or more), but what about the rest of the data? You know it is garbage and you cannot trust it. And on top of that, you don't know which data is right and where are the mistakes (garbage). I would NOT trust the data like that - it is all garbage. :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

As far as I can determine, the OPs time system is BROKEN in that when the employee either clocks in or out, the IN or OUT is not recorded anywhere.

So the OP wants us to help him determine what is IN or OUT simply from Date/Time records, which are inadequate to make such a determination.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[ongke0711]

If for the same date you need a sequence of IN-OUT-IN-OUT-...,

Thank much combo. That's all what I expect to solve my data first.

SELECT TempImportExcel.EmployeeID, TempImportExcel.WorkDate, TempImportExcel.Time, (SELECT Count(*)
     FROM TempImportExcel As X
     WHERE X.EmployeeID = TempImportExcel.EmployeeID
         And X.WorkDate=TempImportExcel.WorkDate
         And X.Time <= TempImportExcel.Time) AS SeqNo
FROM TempImportExcel;

SELECT Query1.*, IIf([SeqNo] Mod 2=1,"In","Out") AS IO
FROM Query1;

Thank you all for advice my case and solution.

 

[SkipVought]

ongke0711, please let us all know if this solves your problem after you run your SQL on your data.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]