I have an Access database for tracking Support Staff attributes and percentage of FTE allocations of Support Staff time to Professional Staff.
Each month I run an Employee query and Posted Jobs query from external systems, and append them to an Employee and Posted Positions table. and then users input employee attributes and allocations not warehoused in our main system. Attached is an Excel file with a process diagram of the main table and query relationships, and the main Form users interface with.
My problem is i'm not sure how to handle the orphan allocation percentages when someone who either allocated out or received allocations falls off the roster.
When I first import the employee and posted position files each month my process steps are:
1. Append to the resident Employee and Posted Position files with a Report Date field showing the period date
2. Query out employees not considered Active for our purposes
3. Union Query the two datasets to get an Active roster of Current and Vacant (Posted) positions for the month, which i use to
4. Create an All Positions Table for the period
5. Query a subset of Support Employees which runs the main Support Employee Form
6. Have a Clock (employee ID) Allocations table run the clock allocations subform
7. Have an Employee attribute table run the employee attributes subform
8. Users assign FTE % allocations to professional staff and populate attribute infomation.
1. Do I run a Leavers and Joiners query after i import the new month's data but before i append them to the resident Employee and Posted Positions tables to get a leavers and joiners report? I find the task more difficult when comparing the appended month to the prior month once they're in the same table, only differentiated by the Report Date.
2. Should I be tableing the data to an All Positions table at all before defining subsets?
3. Currently I'm only downloading Active employees each month, so if their status changes to terminated, their not included in subsequent queries. Should I query all employees so I have the employment status, making the job easier to identify the leavers? The data set would be larger each month though. Its currently about 550. Including all the Terminated employees could double it.
4. I'd like a process to where orphan allocations are queried and sent to the correct manager for reassignment. Right now I'm manually flagging them as Inactive allocation records and query them out so i dont get blank spaces in my reports but i know thats not the answer.