troubleshooting Question

Access Vacation Tracking Database Design

Avatar of Davisro
DavisroFlag for United States of America asked on
Microsoft Access
1 Comment1 Solution116 ViewsLast Modified:
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?

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros