INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Update "In/Out" for employee time sheet.

Update "In/Out" for employee time sheet.

(OP)
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

RE: Update "In/Out" for employee time sheet.

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:

UPDATE MyTable
Set IO = 'In'
WHERE [Time] = before Noon

and then

UPDATE MyTable
Set IO = 'Out'
WHERE IO IS NULL

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update "In/Out" for employee time sheet.

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

(OP)
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 sad ) . So I need to do by myself first to have data in "In/Out" field

RE: Update "In/Out" for employee time sheet.

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.

RE: Update "In/Out" for employee time sheet.

Quote:

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]?

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

(OP)

Quote (Andrzejek)

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

Quote (SkipVought)

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.

RE: Update "In/Out" for employee time sheet.

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

RE: Update "In/Out" for employee time sheet.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

...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.

RE: Update "In/Out" for employee time sheet.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

(OP)
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.

RE: Update "In/Out" for employee time sheet.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

(OP)

Quote (SkipVought)

...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.

RE: Update "In/Out" for employee time sheet.

Quote:

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

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

RE: Update "In/Out" for employee time sheet.

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. sad

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Update "In/Out" for employee time sheet.

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Update "In/Out" for employee time sheet.

(OP)

Quote (combo)

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.

CODE -->

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; 

CODE -->

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

Thank you all for advice my case and solution.

RE: Update "In/Out" for employee time sheet.

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close