Time input by work order

[Annette]

We have a work order database that tracks requests by departments. We
want employees to start entering time spent on work orders. Everything
an employee does in a day is attached to a work order. Employees
should not see what other employees have entered for time so the entry
should only list their data, however, we don't want to make the form
data entry only as they may have to correct an entry if they keyed it
in wrong the day before. Employees could have as few as one entry per
day (8 hours spent on one work order) but most employees will have
several as the time is tracked at 15 minute increments.

How can I create a time input screen that will capture the employee,
date, work order #, amount of time (and a few other items) that would
be the easiest for employees? I first began with a continuous form
that would allow entry for each piece that I am trying to collect but
found some items repeating (like worker, work order #, date).

Any ideas would be greatly appreciated!

 

[John Spencer]

Step one is proper design of the tables. From what you posted I would expect

tblEmployees with fields: EmployeeID (Primary key unique value identifying an
employee), ELastName, EFirstName, and other fields describing the employee

tblWorkOrders with fields: WOid (an autonumber field), WONumber (text),
WOName, WOSource, and other fields describing the work order.

tblTimeSpent: TimeSpentID (autonumber), WOid, EmployeeID, AmountOfTime,
WorkDate, and any other items that are unique to the time spent by an employee
on a specific work order. You might record starttime and endTime instead of
Amount of time to make it simpler for the employees, but a bit more complex to
calculate amount of time.

You might also have a table of departments and a table of employees assigned
to a department with a begin and end date to record when the employee was
working for the department.

Once you have the structure of your data, I would use a form plus sub-form
structure. If you set this up correctly, the records in the sub-form will
automatically be tied to employee on the main form, so the employee
information will only be entered one time.

I would use a Form (single form) filtered to show the employee. Filter the
form based on some kind of employee login. On the form display the employee
information and a control (txtEntryDate) on the form that contains the date
you are interested in working with.

Set the default to today's date and allow the worker to edit it to other dates
if they need to edit records in the past

A sub-form as a continuous form on the main form would display records based
on tblTimeSpent. You would link the form and subform together using
TblEmployees.EmployeeID and tblTimeSpent.EmployeeID and The txtEntrydate
control in the LinkChild and LinkMaster properties of the sub-form control.

I would use a combo-box control to display the needed information from
tblWorkOrders (you could limit work orders displayed to the specific entry
date if you had a begin and end date for work orders), then all you need is a
field to enter amount of time.

That should be enough to get you started.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County