Access Vacation Tracking Database Design

Davisro used Ask the Experts™
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?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Remote Training and Programming
Top Expert 2015
In addition to the Employees table, I see tables similar to the following:

- 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)

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

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

- 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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial