Avatar of Davisro
Flag for United States of America

asked on 

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?

Microsoft Access

Avatar of undefined
Last Comment
crystal (strive4peace) - Microsoft MVP, Access

8/22/2022 - Mon