Access Workflow
Experts,
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.
Thanks
Database-overview.xlsx
2015-04-28-22-18-49.pdf
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.
Thanks
Database-overview.xlsx
2015-04-28-22-18-49.pdf
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I think what i'm going to do is modify my system query to run Active and Terminated employees YTD instead of just Active employees for the current period. Then I can just delete and replace the data set each month instead of appending periods, and employees with status changes from Active to Terminated wont drop out of the data set.
ASKER
I rebuilt the data based on a YTD Employee table of all statuses (Active, Term, Retired, etc) and its working as I need it to. Ill split points as I found all comments helpful. Jeff's "Complex System" concept helped me manage expectations with the stakeholders.
ASKER
Hopefully the attachments will clarify.
I'm also running into locking issues when I want to update an object in the database when other users are in the database. I'm considering splitting it as a solution, although I've never done that before
PA-Analysis-Shell.accdb
PA-Analysis-Shell-Description.docx