Tracking Person's Work? (1 Viewer)

[sara82]

I'm undecided in what I should to to track what an employee does for the day. Currently employees have a 5X7 index card which is printed out fields and they fill it in by hand and turn it in.

Instead of the employees doing this I want to record all of this information in the database.

These are the fields that I will include:
EmployeeName, Book, WorkType, WorkEffort, StartPage, EndPage, StartTime, EndTime, TotalPgsWorked, Comments.

Should I bound all of these fields to the tblWorkLog or should a create unbound fields and create an append query which will add these fields to the table.

I don't want the users to enter their own start time and end time as they might not record it accurately.

Should the time be recorded this way: Have the user open the form, when the form opens that will record the start time. Have the user have the form remained open until the end of the day or when he or she finishes through out the day. When the form is closed that will record the end time.

What is an alternative of doing that though? Have the user open the form record all the info and have the startime recorded. Exit out of the database, when they finish their work, open the database go back to that record and record the end time?

My problem here would be there is no uniqe ID. The user can work on a Book for a week, and can also work on multiple books on the same day. How would they know which record to open.

I'm uncertain how to lay this out and begin it any insight will be greatly appreciated.

 

[Pat Hartman]

When working with Access, it is best to use bound forms.
You can lock the time in and out fields and provide the value with code. Use the BeforeInsert event of the form to save the In date/time. The form should be closed after the start page is entered. There is no reason to keep the form or the database open all day. When the user is ready to enter the end page they open the form again and enter the end page. In the AfterUpdate event of the EndPage field, your code wil populate the out date/time.

Me.StartTime = Now() ' code to store date/time in StartTime

 

[Oldsoftboss]

I'm undecided in what I should to track what an employee does for the day.

Wouldn't be a very complicated Db at our office.
2 tables - Employee and task

Task would be a lookup table with the following entries:

Arrive late
Coffee
Lunch
Talk sport
Discuss weekend
Winge about boss
Leave early.


(Sorry, I just couldn't resist.)

Dave

 

[sara82]

Pat,

Thank you for the advice. I really appreciate it. That is the way I would really like doing it and what would be most efficient way. To open the database enter all the info except the endpage. Close it and when ready to open it again to enter the endpage and if any, comments.

I don’t quite know how to structure this. How will the employee know which record to open to that he or she is working on earlier. Since this is going to be kept daily, there are going to be multiple entries for that TONumber. Also for example, early in the morning the JobType may be OCR and when they finish with that their next JobType for that TO may be Frame Conversion.

Here are my tables:
tblEmployee
EmployeeID
EmployeeLastName
EmployeeFirstName
Group

tblWorkLog
WorkLogID
EmployeeID
TONumber (which is a book)
LogDate
JobTypeID
JobEffortID
Comments
StartPage
EndPage
TotalPgsWorked
StartTime
EndTime
TotalHours

Lookup table
tblJobType
JobTypeID
JobType - (OCR, Doc Setup, Frame Conversion, QA, Final QA)

Lookup table
tbJobEffort
JobEfortID
JobEffort (Current Work, Corrections, Other)

So since it’s a bound form there will be a 1 to many relationship from tblEmployee – EmployeeID as the PK and tblWorkLog EmployeeID as the FK

 

[sara82]

Oops, Dave typo on my behalf I meant I was undecided *how* to track it.

Wish mine was as simple as that!

 

[Pat Hartman]

Assuming the WorkLogID is an autonumber and is the primary key for tblWorkLog, you can order the RecordSource descending by WorkLogID and the last used record will show up first.

If you do not want anyone changing existing records, add code to the form's current event:

If IsNull(Me.EndTime) Then
    Me.AllowEdits = Yes
Else
    Me.AllowEdits = No
End If

Be sure to set the locked property of the date fields to Yes to prevent any entry to those fields at all.

 

[sara82]

Pat thank you for the post. I am now redoing my form with your recommendations.

I have the following

Private Sub Form_BeforeInsert(Cancel As Integer)

Me.Starttime = (Now)

End Sub

Private Sub TotalWorkedPgs_AfterUpdate()

Me.Endtime = (Now)

End Sub

I have a field "LogHours" where I would like to have calculated the difference in time. I search various posts in this forum about this matter and I tried all the recommendations and it doesn't seem to work

I added

Private Sub Endtime_AfterUpdate()
Dim MyTime

MyTime = Format(DateDiff("n", Me.Starttime, Me.Endtime) / 60, "#0.0")
  Me.LogHours = MyTime

End Sub

I get 0

I tried

Private Sub Endtime_AfterUpdate()

[LogHours] = Format([Starttime] - 1 - [Endtime], "Short Time")

End Sub

I also get 0

StartTime and EndTime are Date/Time fields in the table.
LogHours is Number and I changed it to Date/Time to see if I would get anything and I don't.

I'm not exactly sure what it is that is preventing me from getting the hours.

 

[sara82]

I impemented this:

 [LogHours] = CDate([Endtime]) - CDate([Starttime])

And I formatted the Hours text fied to: h:nn:ss

Which now correctly gives me the amount of time.

But now I'm thinking that I should have stored this value as a number because what I need is to get the sum of the log hours so then I divide by 8 to get the number of days.

But with the log hours displayed and formatted as it is now I will not be able to get the sum of the log hours.

How can I correctly get the sum of log hours?

Also I implemented this:

If IsNull(Me.EndTime) Then
    Me.AllowEdits = Yes
Else
    Me.AllowEdits = No
End If

But when the Endtime field is empty and I try to come back to that record to enter the TotalWorkedPages so that the Endtime will be updated it doesn't allow me to enter any data even though the Endtime is null

 

[Pat Hartman]

As long as you are storing the actual start date/time and end date/time, you can always find the difference in some unit, sum that, and convert the sum to a different unit for display.

The AllowEdits code needs to go in the form's Current event. Is that where it is?

 

[sara82]

Pat:

Yes the code is in the form's Current Event

Private Sub Form_Current()

If IsNull(Me.Endtime) Then
    Me.AllowEdits = Yes
Else
    Me.AllowEdits = No
End If

End Sub

 

[sara82]

Pat I am storing the actual start date/time and end date/time as you recommended. I have been able to get the difference of the two as date/time only formatted as h:nn:ss.
I need to however do exactly what you mentioned below find the difference in a number unit sum it up and display in another unit such as days.
When I format the LogHours into a number I get the difference as 0. I searched for this but couldn't find what I'm looking for.

As long as you are storing the actual start date/time and end date/time, you can always find the difference in some unit, sum that, and convert the sum to a different unit for display.

 

[sara82]

Attached is a sample db to demonstrate the problem I am encountering when trying to entering data when EndTime is Null and with calculating the difference of the 2 times to get the LogHours.

 

[sara82]

I found the problem with the

Private Sub Form_Current()

If IsNull(Me.Endtime) Then
Me.AllowEdits = Yes
Else
Me.AllowEdits = No
End If

End Sub


It needs True/False instead of Yes/No

 

[sara82]

I have tried:

[LogHours] = Hour(TimeValue([Endtime]) - TimeValue([Starttime])) + ((Minute(TimeValue([Endtime]) - TimeValue([Starttime]))) / 60)

[LogHours] = DatePart("h", [Starttime] - 1 - [Endtime]) + DatePart("n", [Starttime] - 1 - [Endtime]) / 60

[LogHours] = ([Endtime] - [Starttime]) * 24

[LogHours] = Format(DateDiff("n", Me.Starttime, Me.Endtime) / 60, "#0.0")

[LogHours] = CDate([Endtime]) - CDate([Starttime])

And I still get 0 for Log Hours.
Start Time and End Time are formatted as Date/Time
Log Hours is formatted as Number

 

[Pat Hartman]

You need to use the DateDiff() function when finding the difference between two dates/times. Select minutes "n" or hours "h" as the unit, whichever is best for your purposes.

The default unit when you subtract one date from another is days which would explain the 0.

 

[sara82]

I implemented this:

[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3)

And from the table design view had to format loghours field to text, and everything is calculating correctly now.

 

[sara82]

Pat,
As I mentioned for me to get this working I did the following:

[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3)

I have come across a problem. There are two 15 minute breaks throughout the day. One at 9:45 AM to 10:00 AM and one at 2:45 PM to 3:00 PM. What can I do that if the StartTime and EndTime falls in these times for breaks to exclude them the times of breaks from the StartTime and Endtime of the Employee?

 

[sara82]

For anyone following through I was advised to do the following and it works (excludes the two 15 minute breaks)

[LogHours] = Round((DateDiff("n", [Starttime], [Endtime]) / 60), 3) + (([StartTime] <= #09:45:00 AM# And [EndTime] >= #10:00:00 AM#) / 4) + (([StartTime] <= #02:45:00 PM# And [EndTime] >= #03:00:00 PM#) / 4 )