ClockCard application design

[Mark]

I am designing a clocking card database & found the Microsoft article How to
Calculate Daily Hours Based on Clock In/Clock Out Times here
http://support.microsoft.com/?kbid=237958. It suggests having 3 tables:
Employees (EmpID, Name, etc), EmpDates (EmpDateID, MyDate, EmpID) and
EmpTimes (TimeID, TimeIn, TimeOut, EmpDateID). What I don't understand is
the need for the EmpDates table. When I was drafting my design I just
planned to have tables Employees and EmpTimes(EmpTimeID, DateTimeIn,
DateTimeOut). Can someone explain the reason for the extra table?

Thanks

Mark

 

[TC]

Clearly, the same employee must be able to clock-on at different times
on the same day. Ie. there will be multiple records for the same
empid+date, each one with a different clock-in time (for that employee,
on that date).

It seems to me, that the designer of the MS solution, has tried to
"optimize" the situation by assigning a different autonumber value for
each instance (occurrence) of an empid+date - so he can then use that
autonumber value in the further table, to record the start/end times.

But you do not need to do that. All you really need, is this:

tblEmployee
EmpID (PK)
name, dob etc.

tblWorkPeriod
EmpID ( composite )
Date ( primary )
StartTime ( key )
EndTime

The difference between the two solutions, is the nature of the primary
keys. In the MS solution, you have simpler PKs, but at the cost of one
more table. In my solution, you have a more-complex PK, but one less
table.

Having said all that ...

I now see a critical difference! The MS solution gives you a place to
store any values that are specific to the employee/date combination
*regardless of clock in/out times*. For example, the MS solution would
let you record the fact that an employee's work, on a particular day,
was on the ABC project. In my solution, there is no table for that
information, and you would have to duplicate it in the second table.
Which is quite appropriate, IMO, since the employee might work on ABC
for part of the day, then on DEF for the rest of the day!

In summary, I personally feel that the MS approach has very little to
recommend it - unless you can think of something that you would need to
store for each employee/date combination *regardless of clock in/out
times*.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Mark]

Many thanks for your advice. I have gone with the two table approach and
I'm currently writing the code for the application. The user will swipe
in/out with a clockcard. The code inserts a [TimeIn] into tblWorkPeriod if
the Employee hasn't already clocked in or the employee has already clocked
in and out earlier that day. My problem is sometimes employees work nights
and start at 10pm and finish at 6am. In my current code below the employee
would clock in at 10pm one day but when they went to clock out at 6am, this
would create another ClockIn as they day had changed. Anyway around this?


If DCount("*", "tblWorkPeriod", "[WorkDate] = #" & Date & "# And [EmpID] = "
& lngEmpId) > 0 Then
' User has already clocked in today
strSqlSel = "SELECT * FROM tblWorkPeriod WHERE [WorkDate] = #" &
Date & "# And [EmpID] = " & lngEmpId
Set rs = db.OpenRecordset(strSqlSel)
rs.MoveLast
If IsNull(rs!TimeOut) Then
TimeOut lngEmpId
Else
' User has clocked in and out already today
TimeIn lngEmpId
End If
Else
' Employee clocks in for first time
TimeIn lngEmpId
End If

 

[mnature]

I would suggest going with a two table design, but allow both time and date
for each clocking that the employee does. You could use a field to designate
whether they are going in or out (just look at what the field had last, and
use the other one).

tbl_Employees
EmployeeID (PK)
EmployeeInfo

tbl_EmployeeDates
EmployeeDateID (PK)
ClockingDate
ClockingTime
TimeInfo
EmployeeID

 

[TC]

Yes, I agree. My suggestion was deficient in not recording the date, as
well as the time.

OP, you could us a Date variable for this. As you probably know
already, a Date variable actually holds a date /and time/.

One mopre thing. This is somewhat unsaf code:

WHERE [WorkDate] = #" & Date & "# And ...

Date is converted to a string value, depending on the locale settings
of the current PC. So 1 March 2006 might convert as "1/3/2006", or
"3/1/2006", depending on those locale settings (which you can see vi
Control Panel). But the #...# syntax requires the date to be /AWLAYS/
in american "month first" format, ie. 3/1/2006. So the code might work
on one PC, but not on another. (I just bought a PC, in Australia, which
was set to American settings.)

Better to use the format() function to explicitly control th
conversion of dates, into strings. You can even add the crosshatches
via the formatting parameter.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[MikeD]

BTDT.

The last time I wrote a card swipe app, we had discrete IN and OUT buttons
that were pressed and populated a PUNCHTYPE field. Made it easy to determine
if there were too many of a punch for a day and to pair up shifts that
crossed dates.

Our current app, has IN, OUT, OUT to LUNCH, Back from LUNCH, Start Break,
End Break.

A manager always reviews and approves hours before a paycheck is cut.

Mike