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