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
RonBudget AnalystAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
First - it's really difficult to give solid advice without seeing your table structure. We can only guess at certain things, so our assumptions (and suggestions) may be off the mark a bit.

By "Leavers" and "Joiners", do you mean Employees who have Left the company, and those who have Joined the company?

If so, then if your data is truly relational you won't be able to remove those "Leavers" if they're associated with other tables (and I would assume they are). Of course, we don't really know your structure so it's hard to tell, but in general an Employee would be associated with one or more tables (like a Task table, for example). Often you'd just have an Active field that would default to True. When an Employee went Inactive for whatever reason, you'd toggle that field to False, and the Employee would be filtered out as needed - for example, if you have a form where Employees are assigned to Tasks, you would most likely only include Active Employees in the selection list.

"Joiners" would most likely be added to the Employee table, assuming you want a row for any Employee.

The same process would apply to the Posted Positions table, perhaps - but, again, it's hard for us to know exactly how it's being used. If you need a history of those Posted Positions, then you should not remove any records, and should instead simply Add new records, or Update existing records.

Regarding the "orphan allocation" process - how would you determine an orphaned allocation? If you mean one that has not been "assigned" to an Employee, then I would assume you could easily query your table(s) to get that data - but, again, we don't know the structure, so we really can't say for sure.

If you'd like to upload a copy of your database, with sensitive data obfuscated, we could probably provide more focused advice. Even a blank structure would help quite a bit.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Not really an "answer" here, ...just an attempt at clarification.
Please note that your post here is not really one "question", requiring one direct "answer".
With your four bullets there, ...it is had to determine what the actual question is, ... and what would qualify as a valid "answer"

It seems as though you already have all your structures in place, because you are already at the point of designing our forms.

"Workflow" questions are typically addressed before you start deigning your User interface.
This way, any change would perhaps only affect your tables/relationships

From what I can tell, this may end up being a complex system here.

So as Scott suggests, ...perhaps a sample database (with a full explanation) would be helpful.

JeffCoachman
0
 
RonBudget AnalystAuthor Commented:
Thank you for your comments.

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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should never modify a live, in-use database (or application, for that matter). If you must make changes, you should do so in a test environment. After testing, you should then move those changes to production.

And yes, you most certainly should implement a split architecture where you have all Tables in one file (the Backend) and all other objects in a second file (the Frontend). You'd link the tables in the BE to the FE, makes COPIES of the FE and distribute those as needed to your local workstations - you would NOT place that FE on a share and gives users a link to the same file.

This way, when you make changes, you just deploy an entirely new FE.
0
 
RonBudget AnalystAuthor Commented:
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.
0
 
RonBudget AnalystAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.