We help IT Professionals succeed at work.
Get Started

Access Vacation Tracking Database Design

Davisro asked
Last Modified: 2015-12-11
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
Remote Training and Programming
Top Expert 2015
This problem has been solved!
Unlock 1 Answer and 1 Comment.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE