If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
ClockCard application design
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 |
#2
|
|||
|
|||
ClockCard application design
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 |
#3
|
|||
|
|||
ClockCard application design
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 |
#4
|
|||
|
|||
ClockCard application design
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 "Mark" wrote: 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 |
#5
|
|||
|
|||
ClockCard application design
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 |
#6
|
|||
|
|||
ClockCard application design
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 "Mark" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What Are The Issues With MS Access Client to SQL Server DB | Smithers | General Discussion | 13 | January 9th, 2006 03:14 PM |
Form Design: Combo Box Application | douglas jones | Using Forms | 2 | March 19th, 2005 07:41 PM |
Database on network | kbrad | General Discussion | 7 | March 17th, 2005 06:57 PM |
Application Only Runs Under Admin Account | David Phelan | General Discussion | 1 | October 7th, 2004 12:55 AM |
Msohelp.exe - Application Error | Armando Alvarez | Setting up and Configuration | 0 | October 9th, 2003 08:29 PM |