Access Workflow


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.

DavisroBudget AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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, 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.

DavisroBudget 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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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.
DavisroBudget 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.
DavisroBudget 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.