|
A
paper time sheet is a physical list of values on a piece of paper. An employee
would usually keep the paper that he or she can pick up at any time and
modify it various times during the time period. If people from the accounting
or payroll department wants to examine the time sheet of one particular
employee, they would have to call the employee and request it. This is can
be difficult or problematic if the employee and the company's accounting are not
physically close, which is not uncommon nowadays (it is not surprising anymore for somebody
located in New York to work for an employer who resides in San Francisco, or for
a contractor in Adelaide to work for a company in Sydney). When the time period
is over, such as at the end of the week or at the end of the two-week period,
the employee can submit the time sheet. The time sheet is then sent to the accounting or
payroll department.
An electronic time sheet is accessed using a computer.
Usually, all employees time sheets are stored somewhere in a database on a
computer. As stated earlier, an employee can access it any time, so can the
accounting or payroll department. This means that, at any time, a supervisor can
check the time sheet, for any reason. To make this possible, an application,
namely a database is created, stored somewhere in a common computer such as a
server, and given access to those who
can use it.
One of the advantages of using paper time sheet
is that, since the employee keeps the time sheet, there is no risk of having a
duplicate time sheet. On the other hand, if you create an electronic table of time sheets,
when an employee who wants to fill out his or her time sheet opens it, you need
to make sure that the right time sheet is opened. In the same way, if somebody
from the payroll department wants to check one particular employee's time sheet,
you need to make it possible and easy to locate the right time sheet.
In the solution we are going to apply, we will create a table of employees and
the time sheet they can fill out. In our time sheet, we will create a certain
column, named TimeSheetCode, that will hold a unique number. We will come back
to the role of this column.
|
Practical Learning: Introducing the Time Sheet
|
|
- If you want to practice with this exercise, start Microsoft Access and
create a Blank Database named ynb1
- On the main, click Insert -> Form
- In the New Form dialog box, make sure Design View is selected and click OK
- On the Toolbox, click the Command Button and click the form (if a wizard
starts, click Cancel
- Right-click the button and click Build Event...
- In the Choose Builder dialog box, click Code Builder and click OK
- Implement the event as follows:
Option Compare Database
Private Sub Command0_Click()
DoCmd.RunSQL "CREATE TABLE Employees" & _
"(" & _
" EmployeeID COUNTER(1,1) NOT NULL," & _
" EmployeeNumber varchar(20)," & _
" FirstName varchar(20) NULL," & _
" LastName varchar(20) NOT NULL," & _
" Title varchar(80) NULL," & _
" HourlySalary Currency NULL," & _
" CONSTRAINT PK_Employees PRIMARY KEY (EmployeeID)" & _
");"
MsgBox "A new table named Employees has been added to the database"
DoCmd.RunSQL ("INSERT INTO Employees(EmployeeNumber, FirstName," & _
"LastName, Title, HourlySalary)" & _
"VALUES('88024', 'Matthew'," & _
"'Larson', 'Account Manager' , 32.15);")
DoCmd.RunSQL ("INSERT INTO Employees(EmployeeNumber, FirstName," & _
"LastName, Title, HourlySalary)" & _
"VALUES('25711', 'Patricia'," & _
"'Katts', 'Regional Manager' , 42.15);")
DoCmd.RunSQL ("INSERT INTO Employees(EmployeeNumber, FirstName," & _
"LastName, Title, HourlySalary)" & _
"VALUES('20410', 'Helene'," & _
"'Boileau', 'Cashier' , 12.15);")
DoCmd.RunSQL ("INSERT INTO Employees(EmployeeNumber, FirstName," & _
"LastName, Title, HourlySalary)" & _
"VALUES('91272', 'Peter'," & _
"'Ulm', 'Head Cashier' , 22.15);")
DoCmd.RunSQL ("INSERT INTO Employees(EmployeeNumber, FirstName," & _
"LastName, Title, HourlySalary)" & _
"VALUES('29475', 'Victoria'," & _
"'Canston', 'Cashier' , 14.25);")
MsgBox "A few records have been added to the Employees table"
DoCmd.RunSQL "CREATE TABLE TimeSheets" & _
"(" & _
"TimeSheetID COUNTER(1,1) NOT NULL," & _
"EmployeeNumber varchar(5) NOT NULL," & _
"StartDate datetime NOT NULL," & _
"TimeSheetCode varchar(15)," & _
"Week1Monday varchar(6)," & _
"Week1Tuesday varchar(6)," & _
"Week1Wednesday varchar(6)," & _
"Week1Thursday varchar(6)," & _
"Week1Friday varchar(6)," & _
"Week1Saturday varchar(6)," & _
"Week1Sunday varchar(6)," & _
"Week2Monday varchar(6)," & _
"Week2Tuesday varchar(6)," & _
"Week2Wednesday varchar(6)," & _
"Week2Thursday varchar(6)," & _
"Week2Friday varchar(6)," & _
"Week2Saturday varchar(6)," & _
"Week2Sunday varchar(6)," & _
"Notes text," & _
"CONSTRAINT PK_TimeSheets PRIMARY KEY(TimeSheetID)" & _
")"
MsgBox "A new table named TimeSheet has been added to the database"
DoCmd.RunSQL ("INSERT INTO TimeSheets(EmployeeNumber, " & _
"StartDate, TimeSheetCode," & _
"Week1Monday, Week1Tuesday, Week1Wednesday, " & _
"Week1Thursday, Week1Friday, Week1Saturday, " & _
"Week1Sunday, Week2Monday, Week2Tuesday, " & _
"Week2Wednesday, Week2Thursday, Week2Friday," & _
"Week2Saturday, Week2Sunday, Notes)" & _
"VALUES('20410', '2007/01/01', '2041020070101', " & _
"'0.00', '8.50', '9.50', '8.50', '9.00', '0.00', " & _
"'0.00', '10.00', '9.50', '8.50', '10.50', " & _
"'9.00', '0.00', '0.00', 'Nothing to signal');")
DoCmd.RunSQL ("INSERT INTO TimeSheets(EmployeeNumber, " & _
"StartDate, TimeSheetCode, Week1Monday, " & _
"Week1Tuesday, Week1Wednesday, Week1Thursday," & _
"Week1Friday, Week1Saturday, Week1Sunday, " & _
"Week2Monday, Week2Tuesday, Week2Wednesday, " & _
"Week2Thursday, Week2Friday, Week2Saturday, " & _
"Week2Sunday) VALUES('88024', '2007/01/01', " & _
"'8802420070101', '0.00', '4.00', '6.00', " & _
"'5.50', '6.50', '0.00', '0.00', '4.00', " & _
"'6.00', '6.50', '4.00', '5.50', '0.00', '0.00');")
DoCmd.RunSQL ("INSERT INTO TimeSheets(EmployeeNumber, " & _
"StartDate, TimeSheetCode, Week1Monday, " & _
"Week1Tuesday, Week1Wednesday, Week1Thursday," & _
"Week1Friday, Week1Saturday, Week1Sunday, " & _
"Week2Monday, Week2Tuesday, Week2Wednesday, " & _
"Week2Thursday, Week2Friday, Week2Saturday, " & _
"Week2Sunday) VALUES('94272', '2007/01/15', " & _
"'9427220070115', '8.50', '8.00', '9.00', " & _
"'8.50', '9.50', '0.00', '0.00', '5.50', " & _
"'6.50', '4.50', '6.00', '4.00', '0.00', '0.00');")
DoCmd.RunSQL ("INSERT INTO TimeSheets(EmployeeNumber, " & _
"StartDate, TimeSheetCode, Week1Monday, " & _
"Week1Tuesday, Week1Wednesday, Week1Thursday," & _
"Week1Friday, Week1Saturday, Week1Sunday, " & _
"Week2Monday, Week2Tuesday, Week2Wednesday, " & _
"Week2Thursday, Week2Friday, Week2Saturday, " & _
"Week2Sunday) VALUES('88024', '2007/01/15', " & _
"'8802420070115', '8.00', '8.50', '9.50', " & _
"'9.50', '8.50', '0.00', '0.00', '10.00', " & _
"'9.00', '8.50', '8.00', '8.50', '0.00', '0.00');")
DoCmd.RunSQL ("INSERT INTO TimeSheets(EmployeeNumber, " & _
"StartDate, TimeSheetCode, Week1Monday, " & _
"Week1Tuesday, Week1Wednesday, Week1Thursday," & _
"Week1Friday, Week1Saturday, Week1Sunday, " & _
"Week2Monday, Week2Tuesday, Week2Wednesday, " & _
"Week2Thursday, Week2Friday, Week2Saturday, " & _
"Week2Sunday) VALUES('20410', '2007/01/15', " & _
"'2041020070115', '8.00', '8.00', '6.00', " & _
"'8.00', '6.00', '0.00', '0.00', '8.00', " & _
"'8.00', '8.00', '8.50', '8.00', '0.00', '0.00');")
MsgBox "A few records have been added to the TimeSheet table"
End Sub
|
- Return to Microsoft Access and switch the form to Form View
- Click the button and continuously click OK and the subsequent message
boxes
|
Time Sheet Implementation |
|
To address our problem of an electronic time, we will create a
time sheet in which two pieces of information are required: an employee's number
and a starting period. After an employee has opened a time sheet:
- The employee must first provide an employee number, which we will check in
the Employees table. If the employee
provides a valid employee number, we can continue with the time sheet. If
the employee number is invalid, we will let the user know and we cannot
continue with the time sheet
- After the employee has provided a valid employee number, we will request
the starting period. After entering a (valid) date, we will check the time.
If there is a record that holds both the employee number and the start date,
this means that the employee had previously worked on a time sheet and we
will open that existing time sheet.
After the the employee or contractor has entered a valid
employee number and a start date, we will create a number called a time sheet
code, represented in the TimeSheet as the TimeSheetCode column. This number is
created as follows:
0000000000000
The first 5 digits represent the employee's number. The
second 4 digits represent the year of the start date. The next 2 digits
represent the month, and the last 2 digits represent the day. This number must
be unique so that there would not be a duplicate number throughout the time
sheet.
To make sure the value of the TimeSheetCode is unique for
each record, after the employee has provided a valid employee number and a start
date, we will create the time sheet code and check if that number exists in the
TimeSheet table already:
- If that number exists already, this means that the employee has previously
worked on that time sheet and he or she simply wants to verify or update it.
We will then open the time values for that record and let the user view or
change it
- If there is no record
with the specified time sheet code, we will conclude that the employee is working
on a new time sheet
|
Practical Learning: Implementing the Time Sheet
|
|
- Switch the form to Design View
- Click the button and press Delete
- Save the form as TimeSheet
- Design the form as follows:
 |
| Control |
Label Text |
Name
|
| Text Box |
Employee #: |
txtEmployeeNumber |
| Text Box |
|
txtEmployeeName |
| Label |
Start Date: |
|
| Date Time Picker |
|
dtpStartDate
|
|
Date Time Picker
|
|
dtpEndDate |
| Label |
Mon |
|
| Label |
Tue |
|
| Label |
Wed |
|
| Label |
Thu |
|
| Label |
Fri |
|
| Label |
Sat |
|
| Label |
Sun |
|
| Label |
Week 1: |
|
| Text Box |
|
txtWeek1Monday |
| Text Box |
|
txtWeek1Tuesday |
| Text Box |
|
txtWeek1Wednesday |
| Text Box |
|
txtWeek1Thursday |
| Text Box |
|
txtWeek1Friday |
| Text Box |
|
txtWeek1Saturday |
| Text Box |
|
txtWeek1Sunday |
| Label |
Week 2: |
|
| Text Box |
|
txtWeek2Monday
|
| Text Box |
|
txtWeek2Tuesday
|
| Text Box |
|
txtWeek2Wednesday
|
| Text Box |
|
txtWeek2Thursday
|
| Text Box |
|
txtWeek2Friday
|
| Text Box |
|
txtWeek2Saturday
|
| Text Box |
|
txtWeek2Sunday
|
| Text Box |
Notes: |
txtNotes |
| Command Button |
Submit |
cmdSubmit |
| Command Button |
Close |
cmdClose |
|
- Right-click the form and click Build Event
- In the Choose Builder dialog box, double-click Code Builder
- In the Object combo box, select txtEmployeeNumber
- In the Procedure combo box, select LostFocus and implement the event as follows:
Private Sub txtEmployeeNumber_LostFocus()
Dim rstEmployees As ADODB.Recordset
Dim blnFound As Boolean
' This flag will allow us to know whether the item number was found
Dim fldItem As ADODB.Field
' Since we are only starting, we assume
' that no item number has been found
blnFound = False
' If there is no value in the Item Number text box, don't do nothing
If Me.txtEmployeeNumber = "" Then Exit Sub
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open _
"SELECT * FROM Employees WHERE EmployeeNumber = '" & _
txtEmployeeNumber & "'", _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText
With rstEmployees
' Check each record
While Not .EOF
' Check the name of the column
For Each fldItem In .Fields
' If the current column is EmployeeNumber
If fldItem.Name = "EmployeeNumber" Then
' Check its value
' If the current column holds the employee
' number that the user entered
If fldItem.Value = txtEmployeeNumber Then
' ... then get the record and display
' the full name in the controls
Me.txtEmployeeName = .Fields("LastName") & ", " & _
.Fields("FirstName")
' Set the found flag to true (we will use it later)
blnFound = True
End If
End If
Next
' In case you didn't find it, move to the next record
.MoveNext
Wend
End With
' If the item number was not found, ...
If blnFound = False Then
' ... let the user know, ...
MsgBox "The item number you entered is not in our list of products"
' ... and reset the form
End If
rstEmployees.Close
Set rstEmployees = Nothing
dtpStartDate.Value = Date
txtWeek1Monday = "0.00"
txtWeek1Tuesday = "0.00"
txtWeek1Wednesday = "0.00"
txtWeek1Thursday = "0.00"
txtWeek1Friday = "0.00"
txtWeek1Saturday = "0.00"
txtWeek1Sunday = "0.00"
txtWeek2Monday = "0.00"
txtWeek2Tuesday = "0.00"
txtWeek2Wednesday = "0.00"
txtWeek2Thursday = "0.00"
txtWeek2Friday = "0.00"
txtWeek2Saturday = "0.00"
txtWeek2Sunday = "0.00"
End Sub
|
- In the Object combo box, select
dtpStartDate
- In the Procedure combo box, select CloseUp and implement the event as
follows:
Private Sub dtpStartDate_CloseUp()
dtpEndDate = DateAdd("d", 14, CDate(dtpStartDate))
Dim iDay As Integer
Dim strDay As String
Dim strSQL As String
Dim iMonth As Integer
Dim strMonth As String
Dim dteStart As Date
Dim rstTimeSheet As ADODB.Recordset
If txtEmployeeNumber = "" Then
ValidTimeSheet = False
Return
End If
dteStart = CDate(dtpStartDate.Value)
iMonth = Month(dteStart)
iDay = Day(dteStart)
If iMonth < 10 Then
strMonth = CStr(Year(dteStart)) & "0" & CStr(iMonth)
Else
strMonth = CStr(Year(dteStart)) & CStr(iMonth)
End If
If iDay < 10 Then
strDay = strMonth & "0" & CStr(iDay)
Else
strDay = strMonth & CStr(iDay)
End If
strTimeSheetCode = txtEmployeeNumber & strDay
strSQL = "SELECT * FROM TimeSheets WHERE TimeSheetCode = '" & _
strTimeSheetCode & "'"
Set rstTimeSheet = New ADODB.Recordset
rstTimeSheet.Open strSQL, _
CurrentProject.Connection, _
adOpenStatic, adLockReadOnly, adCmdText
With rstTimeSheet
While Not .EOF
For Each fldItem In .Fields
If fldItem.Name = "TimeSheetCode" Then
If fldItem.Value = strTimeSheetCode Then
txtWeek1Monday = .Fields("Week1Monday")
txtWeek1Tuesday = .Fields("Week1Tuesday")
txtWeek1Wednesday = .Fields("Week1Wednesday")
txtWeek1Thursday = .Fields("Week1Thursday")
txtWeek1Friday = .Fields("Week1Friday")
txtWeek1Saturday = .Fields("Week1Saturday")
txtWeek1Sunday = .Fields("Week1Sunday")
txtWeek2Monday = .Fields("Week2Monday")
txtWeek2Tuesday = .Fields("Week2Tuesday")
txtWeek2Wednesday = .Fields("Week2Wednesday")
txtWeek2Thursday = .Fields("Week2Thursday")
txtWeek2Friday = .Fields("Week2Friday")
txtWeek2Saturday = .Fields("Week2Saturday")
txtWeek2Sunday = .Fields("Week2Sunday")
blnFound = True
bNewRecord = True
ValidTimeSheet = True
End If
End If
Next
' In case you didn't find it, move to the next record
.MoveNext
Wend
End With
If blnFound = False Then
txtWeek1Monday = "0.00"
txtWeek1Tuesday = "0.00"
txtWeek1Wednesday = "0.00"
txtWeek1Thursday = "0.00"
txtWeek1Friday = "0.00"
txtWeek1Saturday = "0.00"
txtWeek1Sunday = "0.00"
txtWeek2Monday = "0.00"
txtWeek2Tuesday = "0.00"
txtWeek2Wednesday = "0.00"
txtWeek2Thursday = "0.00"
txtWeek2Friday = "0.00"
txtWeek2Saturday = "0.00"
txtWeek2Sunday = "0.00"
End If
End Sub
|
- In the Object combo box, select cmdSubmit
- In the Procedure combo box, select Click and implement the event as
follows:
Private Sub cmdSubmit_Click()
Dim rstTimeSheet As ADODB.Recordset
Dim strTimeSheet As String
' If this is new record, then create a new time sheet
If bNewRecord = True Then
DoCmd.RunSQL "INSERT INTO TimeSheets(" & _
"TimeSheetCode, EmployeeNumber, StartDate, " & _
"Week1Monday, Week1Tuesday, " & _
"Week1Wednesday, Week1Thursday, " & _
"Week1Friday, Week1Saturday, Week1Sunday, " & _
"Week2Monday, Week2Tuesday, Week2Wednesday, " & _
"Week2Thursday, Week2Friday, Week2Saturday, " & _
"Week2Sunday, Notes) VALUES('" & _
strTimeSheetCode & "', '" & _
txtEmployeeNumber & "', '" & _
CStr(dtpStartDate.Value) & "', '" & _
txtWeek1Monday & "', '" & _
txtWeek1Tuesday & "', '" & _
txtWeek1Wednesday & "', '" & _
txtWeek1Thursday & "', '" & _
txtWeek1Friday & "', '" & _
txtWeek1Saturday & "', '" & _
txtWeek1Sunday & "', '" & _
txtWeek2Monday & "', '" & _
txtWeek2Tuesday & "', '" & _
txtWeek2Wednesday & "', '" & _
txtWeek2Thursday & "', '" & _
txtWeek2Friday & "', '" & _
txtWeek2Saturday & "', '" & _
txtWeek2Sunday & "', '" & txtNotes & "')"
' If this is an existing record, then, only update it
ElseIf bNewRecord = False Then
DoCmd.RunSQL "UPDATE TimeSheets SET Week1Monday = '" & _
txtWeek1Monday & "', Week1Tuesday = '" & _
txtWeek1Tuesday & "', Week1Wednesday = '" & _
txtWeek1Wednesday & "', Week1Thursday = '" & _
txtWeek1Thursday & "', Week1Friday = '" & _
txtWeek1Friday & "', Week1Saturday = '" & _
txtWeek1Saturday & "', Week1Sunday = '" & _
txtWeek1Sunday & "', Week2Monday = '" & _
txtWeek2Monday & "', Week2Tuesday = '" & _
txtWeek2Tuesday & "', Week2Wednesday = '" & _
txtWeek2Wednesday & "', Week2Thursday = '" & _
txtWeek2Thursday & "', Week2Friday = '" & _
txtWeek2Friday & "', Week2Saturday = '" & _
txtWeek2Saturday & "', Week2Sunday = '" & _
txtWeek2Sunday & "', Notes = '" & _
txtNotes & "' WHERE TimeSheetCode = '" & _
strTimeSheetCode & "'"
End If
End Sub
|
- In the Object combo box, select cmdClose
- In the Procedure combo box, select Click and implement the event as follows:
Private Sub cmdClose_Click()
DoCmd.Close acForm, "TimeSheet"
End Sub
|
- Return to Microsoft Access
- Switch the form to Form View and process a few time sheets
|
|
