For many years I had my employees enter their time and job cost information into a spreadsheet. Each employee had their own password protected workbook. Each worksheet was a month and there was also a summary worksheet. There were two sections to the spreadsheet, time and job cost. For each day they would enter their time classification(s) and the department(s) they worked in. The total time must equal to total departments time. There is a column to calcate overtime if the total of time classications exceeded 7.5.
The following is the columns of the worksheet:
Date, Regular Time, SickLeave, ApprovedLeave, Vacation, LeaveWithoutPay, LeaveNoCharge, Total Time (calculated), Overtime (calculated). Department1, Department2, Department3, Total Departments (calculated), Proof (Total Time  Total Departments).
The summary worksheet compiled the information entered by quarter. Also calculated their remaining time per classification. The calculation is time rolled over from previous year + time available current year  time used current year. Rolled over time and time available are static values entered the first day of each year. time used current year is calculated.
I want convert this into a database. So i can analyze and control the information better. But need some help designing the database. Below is what I have so far:
tbl_Employee
EmployeeID (pk)
EmployeeName
tbl_Department
DepartmentID (pk)
DepartmentName
tbl_TimeClass
TimeClassID (pk)
TimeClass  regular time, sickleave, vacation, etc.
tbl_BeginningTimeCurrentYear
TransactionID (autonumber)(pk)
Year  alway the first day of the year (sk)
EmployeeID (fk)
TimeClassID (fk)
TimeClassHours
tbl_RolledOverTimePreviousYear
TransactionID (autonumber)(pk)
Year  alway the first day of the year (sk)
EmployeeID (fk)
TimeClassID (fk)
TimeClassHours
tbl_TimeSheet
TransactionID (autonumber)(pk)
Date (sk)
EmployeeID (fk)
TimeClassID (fk)
TimeClassHours
DepartmentID (fk)
DepartmentHours
Scenarios:
1. An employee can have one or more time classifcations per day
2. An employee can work in one or more departments per day
Any help or suggestions will be greatly appreciated.
Thanks in advance
Mike
