Time (Labor) Capture Database

[RDSJMS]

I need to create or adapt from a similar database a means to capture time (labor) spent on projects. I've searched for this topic, but have come up empty. Perhaps I'm just not searching on the correct search terminology. Here are the basic requirements:

One department can have many employees, but each employee is a member of only one department. An employee can be transferred from one department to another, and records created when the employee was a member of department 1 need to be retained under that department, while records created after the employee has been transferred to department 2 need to be recorded as such.

Projects can be worked on by any employee in any department. Many employees can work on any project.

Employees need to log in to the database daily and report the time spent on each project. A list of all projects needs to be displayed with the ability to enter the number of hours spent on each.

Reporting to include grouping of employees into departments; time spent by a department in total on each project; who worked on which project and total time spent.

There is a management shakeup here and I'm under a lot of pressure to get this done. I've done a very simple database, but nothing as complex as this.

Any and all help will be GREATLY appreciated.

 

[TTime]

I don't know if this will be adaptable. It's based on a template supplied with Access 2000. HTH Bernard

 

[RDSJMS]

Bernard,

Thank you. It has a lot more than I currently need, but I'll muddle through it and try to make it work.

Randy

 

[The_Doc_Man]

This subject has been brought up many times under various topics.

Basically, your first task MUST be to study normalization up through "3rd normal form." (4th and 5th exist but you don't run into those cases quite so often.) You can "Google-search" normalization and get about a bazillion hits. Pick a college with a name you know and read that college's articles on the subject. Or find ORACLE, DB2, or other makers for detailed articles on same.

OK, assuming you read through that mess, your problem is that you need at least these tables:

Employee - identifies person, including an employee number that is the prime key for this table.

Department - identifies department, including a department number that is the prime key for this table.

EmployeeTransfer - shows when an employee transfers. Has employee number, gaining department, (optional) losing department, date transfer is effective. Transfer-out date is initially set to some future date. Reset this when a person transfers out of a department, perhaps.

Project - shows date project started, describes project. Could include original budgeted hours, though that could lead you to some really ugly situations regarding same. If projects belong to a department, the department number can go here. If projects are owned by the company, then no department codes are needed.

(Bell and whistle) Project Authorization - shows date an employee was added to the list of persons allowed to work on the project. Optional: Shows date after which employee is not available. Intially, set "no longer available" date to some time in the distant future.

Time record - shows employee, date of the entry (= when the time was spent), amount of time (probably hours and fractions). It is OPTIONAL that you include the department number. If the person has flexibility as to project, then the project number must go here.

Validations: A person cannot enter a time record for a department unless the date of the time record is between the person's transfer-in date and transfer-out date. Ditto, cannot charge time to a project unless they have a project authorization record with dates bracketing the work. If projects are owned by a department, can cross-check that person works for the department at the time s/he worked on the project.

Reports: This might be a big, ugly multi-layered JOIN query but you can put breaks in the report by date, employee, project, department, or phase of the moon. The latter only if you have the right library...

Trust me, if you get the data layout properly normalized, the reports will work. If you don't, they won't. That simple.