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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.