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
LVL 1
DavisroBudget AnalystAsked:
Who is Participating?
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.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.