Employee Time Tracking Database Design

[shell159]

I am a relative novice with database design. Currently our company is utilizing a spreadsheet to track employee time records, but we would like to move to a database so that we can use the information to generate more usable reports. We are relatively small with about 25-30 employees. My problem is that I am not entirely sure the best way to break down my information to be able to maintain the information I need.

I need to be able to track the following information:

• Employee ID (last 4 of SSN)
• Employee Name
• Component
• Full Time or Part Time (part time employees need to be able to track the number of hours worked to make sure they don't exceed 1000 hours in the calendar year)
• Hire Date (Full time employees accrue 40 hours of vacation after 1 year as well as 24 hours of personal leave which reset every year on their anniversary date. In addition, full time employees earn 4 hours of sick leave every 28 day schedule)
• Hours worked for the week (Sun-Saturday) (4 time cards per 28 day period)
• Sick leave used
• Vacation used
• Compensatory time used
• Holiday time used
• Personal leave used
• Sick leave accrued
• Vacation accrued
• Compensatory time accrued (calculated based on 28 day period - straight time for 160-171 hours per 28 day period - time and half for hours over 171 in the 28 day period)
• Personal leave accrued
• Running balance for each type of time

The goal is to be able to enter the time worked and any time taken off each week when time cards are submitted. We want to be able to enter an employee id at any given point and tell how much of each type of time they have in their "banks".

Right now we have spread sheet with rows for each of the time categories and columns for each week. Every 4 weeks there is a formula that computes the accrual for the 28 day period. Each employee has their own spreadsheet tab with the same dates, formulas, etc. The spreadsheet gets rather long and complex and doesn't allow for a very user friendly summary of vacation, sick leave, etc. I have looked online, but I am not finding any really good examples of how this can be accomplished. Are we better off staying with our spreadsheet or is there a simple way to organize this data for a database?

Any help would be greatly appreciated.

[June7]

T&A db is one of the more difficult to build. One reason is that most T&A paper forms do not resemble normalized database structure.

Has been topic of numerous threads. Did you search? MS has a TimeCard database template you might download and explore.

Think I would explore use of an OTS package like QuickBooks. If I remember right, it has payroll component. But you don't seem to want payroll, just leave tracking. Don't remember if QB has leave tracking.

In Access > File > New > Employee > TimeCard