Hunting with Queries?

[Coach Ty]

Hello,
I have a couple of ideas for how to accomplish this task. But I'm not sure what the best way would be. So, I thought I would present it to you experts.
Here's the task:
I'm currently filling various positions in my company, according to priority. Using the ranking positions of my employees in various performance categories.
Here's the issue:
The positions are filled according to priority, with the top employee in specific categories. The problem is that one employee can hold the number one ranking position in several categories.

I need my system to place an employee into the specific position and then eliminate that employee's information, so that they will not be placed into any other positions in subsequent steps in the process.

For example:
I have twently employees.
I have ten positons to fill, based on ten categories.
If an employee is ranked #1 in Category A and B, they will be placed into both positions 1 and 2.
I need the system to place the top ranked employee in category A, into position one. Then when it goes to fill the number two position, it will hunt to category B and find the same employee (placed in the previous step) again in position #1. But since this employee has already been placed, it will role down to the next employee, in the ranking order, and place that employee into the next position.
The system will continue through the process until all of the positions have been filled, in the same manner. IE: placing an emloyee into a position and then eliminating that employee from the next step. As each position is filled, each employee is eliminated from the next step, until all steps have been completed.
So, does anyone have any ideas, as to what would be the best way to accomplish this?
I'm eager to find out what you experts can come up with.
Thanks for your help!

 

[David Eagar]

A checkbox in the employee table to indicate Allocated?

 

[Coach Ty]

Thanks for your reply.
However, I'm not sure what your referring to?
I was thinking of a top query to locate the top ranked employee to be placed in a position, with another query that will contain a column for the employees that have been placed. Every subsequent step would have a subtraction of this query column in the formula for subsequent steps or something along those lines. But I'm not sure if that will work?
With an allocated check box, would this have to be checked manually after each step in the process? I was trying to get the steps to be performed in succession automatically.
Can your suggestion accomplish this?

 

[David Eagar]

If the criteria of the query is Allocated = False, and after allocation, it is updated to True, then on the next cycle, this employee would not be available for Allocation to another position

 

[Coach Ty]

Please forgive me if I sound ignorant. I'm really new to access.
Your suggestion sounds like a very interesting concept.
Would the allocation happen as a part of the expression of the query or as a check box, like with your first suggestion?
I can understand check boxes and true/false values.
If I did a top rank query to determine the employee for positional placement, how would the employee be listed as "allocated" after placement? Is allocation a function contained within the expression as a true/false value or are you referring to a check box that is manually checked indicating allocation after placement?

 

[David Eagar]

Can you post your database so I can see your data? This will make it easier to explain - bear in mind, my expertise in VBA coding is virtually non existant. If it were me, I would create a series of action queries and use a macro to run them in sequence - True experts will frown on this suggestion, as VBA coding is the preferred option

 

[Coach Ty]

I'll have to go through the DB to remove the sensitive information before I can post it online.
This DB is being developed to function online. My plan was to develop it using access and then migrating it to MS SQL Server. I realize at some point, I will have to employ an IT professional to develop the forms to function online.
I'm not quite sure how to develop a "macro" yet to run anything. But I think your on the right track. I'll have to do some reading on Macro development and how to allocate. Can you tell me if "allocation" is a function that can be used in an expression or if you were referring to designating a True/False check box to indicate if an employee has been allocated or not?

 

[David Eagar]

I am thinking just as a yes/no box - the employee is allocated or not

 

[Coach Ty]

Hmmm so what your suggesting is to manually check the allocation box after every step of the process, to eliminate that employee from being placed in the next step?
Yes, I do think that would work. However, I would like for the entire process to be automatic.
I've read up on Macors and I believe that will be the way to accomplish what I need. A Macro group will run a series of macros in succession. The trick will be to eliminate every employee from the next step after placement.
I was thinking that if I do a top rank query, for placement, for example: Position one = Top Rank of Category A, could an expression be written for the next step (macro), which would subtract the employee now occupying position one, from the next step? For example: Position two = Top Rank of Category B Minus (-) Position one.
If something like this would work, I could write each subsequent expression to subtract the positions preceeding it. Or I could also create a query containing a column that will contain the allocated employees, already placed. Therebye subtracting this column from each subsequent step.
The question is whether or not it's possible to write an expression that will subtract the employee occupying the prior positions or "allocated" column?

 

[David Eagar]

Given the nature of what you are designing your database for, I don't think my solution is advanced enough. It is more a technique that could work and ensure your tables have all the tools necessary to be able to obtain your goal.

Ideally, it would be VBA code with loops to run through the cycles of employees and positions. As I've said, I have no expertise in VBA, other than I know this sort of thing can and should be done here

I'll build a model of what I mean & you can check whether it will have any 'legs' for you

 

[Coach Ty]

I really appreciate your help.
As you can see, I barely know some things about Access, and nothing about VBA.

 

[David Eagar]

This sounds like a classic case of the blind leading the blind, but I'll give it a go

 

[David Eagar]

This is cumbersome, but at least should show the principle - open the 2qryEmployeeAllocation which selects the most appropriate employee per position, the run the Allocate macro, which then updates the Allocated checkbox

Run 2qryEmployeeAllocation and the next most appropriate employee is selected, repeat until you have no unallocated employees anymore & you must do your bit for the Global Economic Crisis and hire more staff!

 

[Coach Ty]

Thanks a lot for your help ... if it works maybe I'll be able to hire more staff to do this type of stuff on the weekends, instead of me having to do it myself

 

[Coach Ty]

Hi David,
I went through the file you sent me and I believe it would be easily executed this way. It would appear that through your form, you would designate the position, run the query and then check the allocated box. Is this correct? Then you would designate the next position, run the query again and so on?
I'm unable to run the macros. It keeps giving me an error code, saying that it must be associated with an updatable query?
I was wondering if it's possible to assign a true value to the allocated check box as a part of the query result? Then run a group macro to completed the steps?
Is it possible for Access to assign a true value to a check box with a query or as part of a query expression?
Thanks again for your help.

 

[David Eagar]

Curious about macro not working (double checked & works OK for me) It just runs the Append qry and then the Update qry - you could run these manually & see if you get error

Is it possible for Access to assign a true value to a check box with a query or as part of a query expression?

Not sure I follow this, can you clarify your ideas on true value? If something more than yes/no is required, building a combo box with as much data as you need is pretty straight forward

The 'logic' of my sample was the highest rated employee was identified per position and the macro is to go through the process of allocating those employees and marking the Employee table as Allocated = Yes

Rerun the cycle & the next highest available employee would be allocated

 

[Coach Ty]

For some reason I'm getting the same error when I try to run any of the macros manually.
What I was saying about a query assigning a true value, was basically a way for the system to simply designate or check the the yes/no box to a "yes" value. rather than the user having to do it manually. The system could then proceed through the steps and fill all of the positions automatically. Subsequently adding a Yes or true value to the employee information after each step.
Since I'm having trouble running the macros, I'm unsure, but it sounds like this may be you've done with your sample?
Are you saying that your macro will assign an "=yes" value to the employee information, in the table after each time the macro is ran?
If so, maybe you can tell me how to design or write the macro?
This is what I was basically trying to ask, in my last post. Whether or not there a way for the system to check the box or designate a "yes" value, as a step in the process?
If so, the macros can be ran in succession as part of a macro group, to accomplish the entire process.

 

[David Eagar]

A fully 'automated' system is what you are after and should be possible, although it will require VBA skills I don't possess.

Why it doesn't work as such in the model supplied is an Access system thing where to find the highest rating employee, there is a Group By query to finf the Max rating per Position - once you use a Group By in a query you cannot do an Update with it. Very frustrating, but that's the way it works. Ideally, it would find the highest rated employee, tick the Allocated box and then move on to the next allocation

Again, what I submitted is not the long term solution for you, more a procedure to step through to check the process and your table structure.

 

[Coach Ty]

What I presently have are skill categories that are based on calculated expressions. These categories contain all of the employees and they have been grouped according to the expressions used and ranked in desending order.
I was going to base a top rank query on these categories to obtain the top ranked employee in each category.
Since the grouping has been accomplished prior to this step, in a seperate query, would it be possible to have the system tick the allocated box, as part of this top rank query?
Then run a group macro that contains a group of similar queries?

 

[David Eagar]

The only way I know is to append the results of the grouping query into a table, then extract it into the queries run in the macro