Comparison of Candidates against a given Position

[diofree]

Hi everyone,
I have an HR scenario here and wondered how experts would approach it.
Suffice to say I am using Access on the back end (to be used by HR admin) and SharePoint on the front end (to gather data from users).

SETUP

-Let's say I have a table in Access which has a list of the positions in my organization (eg. CEO) and the criteria required to perform that role (eg. Leadership Rank 5).

-We have a separate Access table which houses current employees and their positions (linking back to the positions table).

-Finally we have an evaluations table which contains fields filled out (via SharePoint) by other users who rate employees against a set list of metrics. I'm still not sure how I will do this but I can get to that later.

REPORT

Essentially what I want to be able to do in Access is have some sort of report spit out who the "top matches" would be for a given position if the current incumbent were to resign or pass away. To do this, Access would presumably match the required skills (eg. Leadership 5) against rated skills or current employees (eg. Mark is viewed as exhibiting Leadership but at level 4).

First Step: I know I am going to get tripped up when it comes to the ranks here, but maybe we can start by keeping things simple and assume that the questionnaire just asks whether or not Mark has Leadership.

Second Step: If 1 seems possible, then maybe we can move on to a more complex scenario where ranks/calibre of each competency need to be in the mix as well.

Third Step: How would I go about allowing multiple peer reviews of someone's capacity, getting those averaged in some way in Access and comparing these averaged values against the desired skill set?

Any suggestions?


EXAMPLE

Table1 - Employees
Fields: LastName // FirstName // Address // Hire Date // Position // Salary
Records:
1) Johnson // Mark // 123 Taffy Lane // 13 June 1980 // Janitor // $56,000
2) Stuart // Joe // 132 Taffy Lane // 14 June 1980 // Marketing Lead // $65,000
3) Morelli // Mike // 156 Taffy Lane // 15 June 1980 // IT Technician // $72,000

Table2 - Position Info
Fields: Position // SalaryMIN // SalaryMAX // LeadershipRank // Organization Rank // Friendliness Rank
Records:
1) CEO // $100,000 // $200,000 // Leadership5 // Organization5 // Friendliness5
2) Janitor // $50,000 // $60,000 // Leadership1 // Organization3 // Friendliness3
3) Marketing Lead // $60,000 // $75,000 // Leadership4 // Organization3 // Friendliness4
4) IT Technician // $70,000 // $80,000 // Leadership3 // Organization 4 // Friendliness5

Table3 - Aggregated Surveys
Fields: LastName // FirstName // LeadershipRank // Organization Rank // Friendliness Rank
Records:
1) Johnson // Mark // Leadership5 // Organization1 // Friendliness2
2) Stuart // Joe // Leadership2 // Organization1 // Friendliness3
3) Morelli // Mike // Leadership5 // Organization4 // Friendliness5

Report
Somehow shows that Mike Morelli is the best fit to replace the CEO.

Thank you kindly!

 

[plog]

, getting those averaged in some way

That's the real sticking point. Sounds like you don't have a methodology for doing so. You know all the variables that go into the formula, but you haven't given solid thought to what the formula is.

The example you gave was too simplistic--Mike outscores the others in all categories. You need to examine a few that are more in the gray area. What if his leadership was 1? Is he still the best CEO? What if he was 3's across the board?

You really need to formalize your formula. Ultimately I believe you need to write a custom function that you pass an Employee ID and Position ID to and it returns a number scoring that person for that position. You coudl then run a query and get the top 5 scores for each position and generate your report.

Again though, you don't have an Access issue right now--you have an offline issue. You need formalize what is important to determining how to rate people.

 

[diofree]

Hi Plog,
You are absolutely correct, the formula eludes me and it is not an area where I have alot of experience. I am new to Access in general. I had thought of a similar idea of coming up with a score, and using that score to rank candidates. I'm glad you had the same thought as it means I should pursue that route further.

Before I even get to that point, I'm not sure how I can solicit feedback from several people and have that data get aggregated back into Access. Any thoughts on this?

I realize the example is overly simplistic, i did it that way simply to illustrate the premise. As you say, real world examples will be much more complex.

 

[plog]

I would start by eliminating the term/concept 'Rank'. I would replace it with 'Weight' (for Positions) and 'Score' (for employees). I would scale Weights to 1 an Scores to 10.

Then, I would apply Weights to each position:

Position, SalaryMin, SalaryMax, LeadershipWeight, OrganizationRank, FreindlinessRank
CEO, 100000, 200000, .6, .3, .1
Janitor, 50000, 60000, .1, .8, .1
SalesMan, 75000, 90000, .1, .2, .7

Note how within each record all the Ranks add up to 1.

And Scores to each Employee:
LastName, LeadershipScore, OrganizationScore, FriendlinessScore
Jones, 7, 4, 8
Smith, 2, 4, 3


Note: all those Scores are on a 1-10 scale.

Then to compute the EmployeePositionScore you multiply the scores for each employee by the Weights for each position:

CEO
Jones = (.6*7) + (.3*4) + (.1*8) = 6.2
Smith = (.6*2) + (.3*4) + (.1*3) = 2.7

Janitor
Jones = (.1*7) + (.8*4) + (.1*8) = 4.7
Smith = (.1*2) + (.8*4) + (.1*3) = 3.7

Janitor
Jones = (.1*7) + (.2*4) + (.7*8) = 7.1
Smith = (.1*2) + (.2*4) + (.7*3) = 3.1

I don't know if that's the final answer to your formula, but a good place to start.

 

[diofree]

Plog!
Thank you very much, this is truly very helpful and I think will get me on the right path. I really appreciate your help! I may be back when I understand their HR setup better, and I hope you will still be available as your help has been invaluable.

Thanks again.

 

[diofree]

I would start by eliminating the term/concept 'Rank'. I would replace it with 'Weight' (for Positions) and 'Score' (for employees). I would scale Weights to 1 an Scores to 10.

Then, I would apply Weights to each position:

Position, SalaryMin, SalaryMax, LeadershipWeight, OrganizationRank, FreindlinessRank
CEO, 100000, 200000, .6, .3, .1
Janitor, 50000, 60000, .1, .8, .1
SalesMan, 75000, 90000, .1, .2, .7

Note how within each record all the Ranks add up to 1.

And Scores to each Employee:
LastName, LeadershipScore, OrganizationScore, FriendlinessScore
Jones, 7, 4, 8
Smith, 2, 4, 3


Note: all those Scores are on a 1-10 scale.

Then to compute the EmployeePositionScore you multiply the scores for each employee by the Weights for each position:

CEO
Jones = (.6*7) + (.3*4) + (.1*8) = 6.2
Smith = (.6*2) + (.3*4) + (.1*3) = 2.7

Janitor
Jones = (.1*7) + (.8*4) + (.1*8) = 4.7
Smith = (.1*2) + (.8*4) + (.1*3) = 3.7

Janitor
Jones = (.1*7) + (.2*4) + (.7*8) = 7.1
Smith = (.1*2) + (.2*4) + (.7*3) = 3.1

I don't know if that's the final answer to your formula, but a good place to start.

Hello again Plog (and Forums!),
I have put together a DB file with some of what you describe and I think I have gotten as far as I can and need help with the next step.

FYI: I have attached the file for reference.

Quick Summary: I have three tables (Employees, Positions, and Evaluations). People submit evaluations as you describe, by choosing an employee to evaluate, and selecting a score (1-10) to rate that person on a given attribute. I then have a query which averages up an individuals scores across the different attributes (this is in case we want to have multiple people evaluate someone).

I need help with the next Query. How do I get Access to give me something comprehensible which shows a list of the Positions, and under each, a list which ranks all employees who have been evaluated. This would work exactly the way you described where a person's name would appear under each position, the difference being the weights multiplied against their score would be different each time.

This is probably quite simple but is eluding me.

 

[plog]

First, its always a pain to work with table/field names with non-alphanumeric characters (e.g. spaces). So I would eliminate all spaces in field names. Just going to make all the coding and queries easier down the line. Just use capitalization instead (Start Date - > StartDate). Be sure you do that for your query field names as well.

Also, its best if you also prefix your ID fields with what the ID is for. THen when you build queries it makes it easier to differentiate them instead of having 3 different fields that are each named 'ID'.

Now, assuming those changes, for your data it's going to take 2 sub queries. Here's the first, which gets the Averages of each evaluation:

SELECT Evaluations.Employee, Avg(Evaluations.LeadershipScore) AS Leadership, Avg(Evaluations.[Critical ThinkingScore]) AS CriticalThinking, Avg(Evaluations.ProfessionalismScore) AS Professionalism
FROM Evaluations
GROUP BY Evaluations.Employee;

Name this query 'BestCandidates_sub1'. It's just like the one you currently have, but with better names. Next use this SQL to get the scores of every employee for every position:

SELECT Positions.PositionID, BestCandidates_sub1.Employee, [Leadership]*[LeadershipWeight]+[CriticalThinking]*[CriticalThinkingWeight]+[Professionalism]*[ProfessionalismWeight] AS EmployeeScore
FROM BestCandidates_sub1, Positions;

Name that one 'BestCandidates_sub2'. Then finally, your query which ranks all those employees by those scores:

SELECT BestCandidates_sub2.PositionID, 1+DCount("[PositionID]","BestCandidates_sub2","[PositionID]=" & [PositionID] & " AND [EmployeeScore]<" & [EmployeeScore]) AS EmployeeRank, BestCandidates_sub2.Employee, BestCandidates_sub2.EmployeeScore
FROM BestCandidates_sub2
ORDER BY BestCandidates_sub2.PositionID, 1+DCount("[PositionID]","BestCandidates_sub2","[PositionID]=" & [PositionID] & " AND [EmployeeScore]<" & [EmployeeScore]);

These queries will become slow the more evlauated employees and positions you have. That's just the nature of finding the score of every position for every employee and of ranking any set of data.

 

[diofree]

Thanks so much Plog!
I really appreciate the 'best practices' advice, this is very valuable.
Is there a good way to rename fields after the fact, or must it be done right from the beginning? I'm getting errors trying to remove spaces.

 

[diofree]

NVM got it working, had to delete the query, i think it didn't like losing the link to the old name. Will try this out now. Never done SQL but i think I can figure out what to do given what you left me.

 

[diofree]

Hi again!
Only two questions.

a) Potential issue with last function? It seems the scores are getting ranked in reverse order, small scores are getting ranked #1 and larger score getting #2?

b) I'd rather it show position name rather than ID. So i changed sub2 function to pull in the Position title rather than ID, but this then breaks the heck out of your third function and when I try to fix that function to refer to Position instead of PositionID, it gives me some odd errors. Any clue what I'm doing wrong.

PS. This is a random other question but i'll put it down now anyway. This is probably super basic but I don't get it. On my Employees table I created a new column called DisplayName which aggregates Last Name with a comma followed by first name (eg. Davis, Joey). I'd want this column to be the one that is getting picked when people are doing evaluations (and therefore trickle down to the final query as well) but I get an error that i can't build a relationship to a calculated field. What gives?

 

[plog]

a. I put the wrong operator in my EmployeeRank field of the last query. In the last query, open it in design view and use this:

EmployeeRank: 1+DCount("[PositionID]","BestCandidates_sub2","[PositionID]=" & [PositionID] & " AND [EmployeeScore]>" & [EmployeeScore])

b. You shouldn't have calculated values in your tables. You should do all that calculated stuff in queries. To get the full display name you want, in the last query, bring in the Employees table, link it to BestCandidate_sub2 via the Employee field and then calculate the display name as you like.

 

[diofree]

Got it working!! Yay! This is great, thank you so much Plog!

 

[diofree]

Hi again.
Wondering if this is possible in access. Rather than having the query run and show all candidates and all positions, is it possible to have a dynamic query that returns all results for one given employee, or all results for one position? Could a drop down or something similar be added that would allow the query (and report) to run based on an input parameter as described above?

Thank you!

 

[plog]

Yes. Build another query on top of the one you have and apply your criteria to it.

 

[diofree]

OK. As for the criteria. Would I need to create 50 queries, one for each employee. Or is there a smarter way. Could you prompt the user somehow on which employee they want the query to run for? Would I need to use a method like this? Here

 

[plog]

No, you wouldn't create 50 queries. It really depends on what you are trying to accomplish. Here's what I do:

1. Create a report off of the query you have. It will show everything.
2. Create a form with drop downs for every field you want to filter by.
3. Put a button on the form that says 'Run Report'
4. Add code to the button so when clicked it opens the report and filters it appropriately.

Obviously #4 is the hardest part and will require VBA coding. I suggest you work towards what I laid out and then start a new thread in the form forum for help with any issues you encounter.

 

[diofree]

Thanks Plog I will do that!

 

[diofree]

Hi again,
New request came in today for the comparison feature.

What they want is the ability to specify upfront (in the Employee) table, which 3 positions (jobs) are particular employee is driving toward.

The query will then spit out a report for that individual and indicate, only for the 3 identifed positions, what their rank was for each out of the the whole pool. I'm hoping that query is possible.

If so, to enable it, could I store the Identified Roles information in one field in the Candidates table (linking back to positions table for integrity), or would I need 3 different fields, one to store each "identified role" for that person (each linking back to positions table for integrity).

Any advice would be helpful!
thanks!

 

[plog]

If you wanted to store the potential positions of each employee you would do it in 1 table, structured like so:

PotentialPositions
PotPos_ID, autonumber, primary key
ID_Employee, number, link to Employees table
ID_Position, number, link to Positions table

Then for 3 potential positions for an employee you would add 3 records (1 for each position).

To get the data from the query we built earlier, you would build another query. You would use that query and the PotentialPositions table, linking them apporpriately.

 

[diofree]

Wow, I would not have thought of a new table. So if someone here proposes storing the data in a column (or multiple) in the Employee table, I should dissuade them, correct?

Thanks again. I hope this is the last time i need to bug you, but I doubt that will be true!