Date / Time Issue

[tpickrel]

I am creating a clock in, clock out db for my boss and have created a table with the fields, Employeeid (PK) , DatetimeValue (Pk), TimeIn (Yes/No) TimeOut (Yes/No) - I populate the datevalue with the long date value from a form through a macro setvalue, The employee selects the time in or time out from radio buttons on the same form. The problem I have is that this creates seperate records for the clock in and clock out so when I try to query them to get hours worked I cant seem to get them to calculate because they are on different lines, any help, bare in mind the employee might clock in and out several times a day. Thanks
ill check back tommorow or you can email me at tpickrel@hotmail.com

 

[DCrake]

I day should = 1 record. Do not have each clock event in a different record. Design your table so that it has the

ClockEventID = PK
EmployeeId = FK
ClockDate
ClockInStart
ClockOutLunch
ClockInLunch
ClockOutStop
.....

If the system needs to contend with 24 hours shift patterns then you need to store the date and time for each clock event, however if it is for days only then the time only will suffice.

As soon as the employee clock in for the first time in a day it becomes an append action. Any further clock events for the same employee for that day will then become an update event.