Time & Attendance

[mousemat]

Hi all

Been away from Access for a number of years so bear with me

I have been tasked with creating a simple Access T&A system. The company use time clocks whereby the data is polled automatically to a SQL database.

I can connect to the tables no problem and link to the data etc.

Each employee is to clock in or out depending upon whether it lunch time etc. so it might go something like this

Clock in first thing in the morning
Clock out for lunch
Clock back in after lunch
Clock hout for home time

Technically there should be a mimimum of four entries in the table for each employee for each day. (Obviously we know some people forget to clock in or out but thats not the issue at the moment.)

I have used the splitfield function to split the date and time field into their own fields using a query. Ive not added these to the main table yet but will want to do this 'on the fly'

My problem is I can't get my head round the time calculations between the four entries. These could also go over midnight just to add fuel to the fire.

Here is the sample data for one employee, obviously there will be more employees and this will need to be done over differing dates etc.

Any ideas would be great

 

[DCrake]

This first thing you must address is changing the structure to horizontal from vertical. In other words have one record for each day
Date: TimeIn TimeOut TimeIn TimeOut

Use the DateDiff() function to calculate the Elapsed times between In and Out

 

[mousemat]

Mmmm thanks for that David

The softweare that polls the data pouts it in that structure in the datatbase, we cannot change this. Is there an easy way to change the structure in Access? I guess by way of query/tables etc

 

[DCrake]

The reason I made this comment was that you cannot 100% say that there will be 4 records for each person per day, as you indicated. How do you know which one is missing?

Did they forget to clock in when they arrived?
Did they forget to clock out a lunch time?
etc
etc

Some how SQL needs to tell you which time relates to which event then you will be able to create a union query to rotate the data.

There are other issues to think about but you need to get these ones resolved first.

 

[Zigzag]

My question would be 'How do you identify which record is clock in and which record is clock out?

Until you can determine that its going no where.

 

[mousemat]

On the issue of people forgetting to clock in, the company in question are saying that they would have to manually input the missing data. So basically if there isn't 4 entries then it should be ignored.

So for the interim, i guess we can assume that all clockings are there and none are missing. That being the case, a union query is something ive not tackled before.

 

[DCrake]

That only complicates the matter even further. Why? because the first task would be to isolate only those records that that have 4 entries per day, having said that how do you handle night shift workers they will clock in on one day, then clock out for lunch on the next day, then clock back in after lunch and finally clock out at the end of the shift.

I suggest you import the data as it stands into a temp table as a working copy. Then manipulate the data to append it to another table that is rotated horizontally. You need to sort the temp table in such a way that you will be able to split the data between employee by date/shift.

deleting all records that do not appear in blocks of 4. It's going to be interesting.

The real issue is your staff not clocking in/out this is normally a compulsary woking practice subject to discaplinary actions. Garbage In Garbage Out.

 

[Zigzag]

Adding to what Dave has posted....

I would create a new table that holds 'assumed time zones' and link each employee to a time zone. If the clock time falls within the time zones it would asume it as a clock in or clock out accordingly and create the new table based on these rules.

 

[mousemat]

Hmmm. I'll have a rethink and see what I can do

Thanks for the advice. Your right, it's going to be hard work