Access Vacation Tracking Database Design

I'm building an Access Database to manage Accrued Vacation and Sick Time balances, but I'm having a hard time with what the table structure needs to be. We track vacation and sick time on an Exception Basis, so instead of recording hours worked, employees fills out a Monthly Timesheet and just enter whole or half days NOT worked.

Data sets that I have are:
1. Employees and attributes, with calculated Monthly Accrued Vacation days and Sick days for each employee based on seniority. For example:
EmplID Period AccrVacDays AccrSickDays
1000 1 2.08 1.0

2. Monthly timesheets from each employee with number of days not worked and why. The timesheet data for all employees are compiled into a Master Spreadsheet.
EmplID Period VacDaysUsed SickDaysUsed
1000 1 1.0 0.0

3. Included in the master sheet are carryforward balances from the prior year which I'm thinking should be entered as Period 0 in the database.
EmplID Period VacBalDays SickBalDays
1000 0 25 4

What I'm not sure of is should I just have all of this in one table as such:
EmplID Period AccrualType Category Days
1000 0 CarryFwd Vac 25.00
1000 0 CarryFwd Sick 4.00
1000 1 Accr Vac 2.08
1000 1 Accr Sick 1.00
1000 1 Used Vac 1.00
1000 1 Used Sick 0.00

And then calculate the monthly balances in a query as: [CarryFwd Vac (Per 0)] + [Per 1 AccrVac] - [Per 1 Used Vac Per], which is:
EmplID Period Type Bal
1000 1 Vac 26.08 (25+2.08-1.0)
1000 1 Sick 5.0 (4+1-0)

Can anyone help me with the necessary table structure?

Thanks

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 25

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented: 2015-12-10

In addition to the Employees table, I see tables similar to the following:

TimeTypes
- TimeTyID, Autonumber, PK (Primary Key)
- TimeCatID, Long Integer, FK (Foreign Key to TimeCats)
- AcrTyID, Long Integer, FK (Foreign Key to AcrTypes)
- TimeTyCode, text (ie: AccrVacDays, AccrSickDays, VacDaysUsed, SickDaysUsed
- TimeType, text (ie: Accrued Vacation Days, Accrued Sick Days, etc)

TimeCats
- TimeCatID, Autonumber, PK (Primary Key)
- TimeCat, text (ie: Vac, Sick)
- etc

AcrTypes
- AcrTyID, Autonumber, PK (Primary Key)
- AcrTy, text (ie: CarryFwd, Accr, Used)
- etc

EmpTime
- EmpTimeID, Autonumber, PK (Primary Key)
- EmpID, Long Integer, FK (Foreign Key to Employees, assuming it is also Long Integer or Autonumber)
- TimeTyID, Long Integer, FK (Foreign Key to TimeTypes)
- dtmTime, date/time, date (and time) for the Time entry
- NbrDys, Long Integer, number of days
- NbrHrs, Long Integer, number of hours

EmpBalances (records can be calculated anytime; stored as reporting table for better performance, could be done on a scheduler)
- empBalID, Autonumber, PK
- EmpID, Long Integer, FK (Foreign Key to Employees)
- dtmBal, date/time, balances as of this date/time
- etc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

Access Vacation Tracking Database Design

Who is Participating?