Attendance Database Relationships

[WinIDS]

Hi all,

First off, I'll admit that I'm rather new at this, so please let me know if I'm doing this right.

I'm building a very simple database to keep track of the clock-in and sign-out times for a list of employees.

I have tbl_checkin that contains the IN time, tbl_checkout to manage the OUT time, and tbl_employeenames for name reference. I put the checkin and checkout date/times in separate tables since they are to be filled automatically according to system time, i.e. =Now()

What I'm thinking of is this:

tbl_Checkin
Checkin_ID
Employee_ID
DateIN
TimeIN

tbl_Checkout
Checkout_ID
Employee_ID
DateOUT
TimeOUT

tbl_EmployeeNames
Employee_ID
Employee_Name

.. which might not be the best solution as it becomes a little difficult to link them together, especially if DateIN and DateOUT are different.

Any help would be appreciated. Thanks!

 

[jzwp22]

I would recommend having just 1 table for all IN/OUT activities. You can just have a field to say whether the employee is checking IN or OUT (the activity). Also, you can store both the date and the time within one field. It will make your calculations easier rather than dealing with separate date and time fields.

tblTransactions
-pkTransID primary key, autonumber
-Employee_ID
-Activity
-ActivityDateTime

 

[WinIDS]

Thanks for the reply.

However, if we store the data in that format, how can the corresponding IN-OUT dates/times be linked together? I plan to add an overtime calculator as well, which would require both the IN and OUT times for the same shift.

 

[MarkK]

I agree with jzwp22. Your problem is exactly the same as a bank account with debits and credits. Time here is like dollars, and ins and outs are withdrawals and deposits.
They are linked by virtue of the fact that they happen consecutively in the time dimension for a particular employee.

 

[WinIDS]

Hmm.. I feel like an idiot, but after fiddling around with the tables, I still can't figure out how to calculate overtime with the data fixed in this format.

Could you provide a brief outline of how to calculate the difference between a matching IN and OUT time? Thanks in advance.

 

[jzwp22]

In order to calculate the time difference between the IN and corresponding OUT records you will need a nested query. I would probably create a query that returns the IN date/times for each employee sorted by EmployeeID and then the activitydatetime field. Then within that I would nest a query that selects the next OUT time applicable to that employee. Once you have both values, you can use the datediff() function to find the difference in minutes between the IN and OUT values.

I've attached a sample database that illustrates that query (query name: qryTimeCalcs) that does the calculation. From there, you can create a query that sums the time worked. Instead of having an activity field that had IN or OUT, I set up a related table to hold possible activities and used a foreign key in the transaction table.

Is it possible for an employee to miss either clocking IN or clocking OUT? If so, then you will have to check (in your data entry form) that the last record entered for the employee has the opposite activity compared to the record that they are trying to enter.

 

[MarkK]

It's simpler than that. A date/time value is stored as a number with days to the left of the decimal and times to the right. The difference between one date/time and another date/time is, like any other difference, a simple subtraction, which is a simple negative addition.
So with every 'In' record, store a -1 and with every 'Out' record store a 1, maybe call it 'Direction.' Then Sum the ( DateTime * Direction ) for a certain emplooyee for a particular time period and you'll have calculated the time worked. Then apply the overtime rules for that amount of time.