Comparison of Candidates against a given Position
- Thread starter diofree
- Start date
Update:
(Sorry!)
What if there was no longer the link between Employees and Positions, by there being the lookup from Employees to the Positions table? Reason I ask is they are telling me they don't want to bring in the entire list of positions, as they number is in the thousands. They wanted to just bring in the list of KEY Positions into that table. As a result, the Position field in the Employees table would include a much larger list of possibilities than is available in the Positions table - therefore invalidating the lookup. No way to make the "Identified Positions" (as discussed yesterday) the link? Because as you said, no way to put that data in one field...
I'm stuck....
(Sorry!)
What if there was no longer the link between Employees and Positions, by there being the lookup from Employees to the Positions table? Reason I ask is they are telling me they don't want to bring in the entire list of positions, as they number is in the thousands. They wanted to just bring in the list of KEY Positions into that table. As a result, the Position field in the Employees table would include a much larger list of possibilities than is available in the Positions table - therefore invalidating the lookup. No way to make the "Identified Positions" (as discussed yesterday) the link? Because as you said, no way to put that data in one field...
I'm stuck....
Sorry!
My limited understanding of basic database design is that in a setup with tables such as
-Company Jobs
-Company Employees
You want a way to link the data between someone in the Employees table, to a unique Job in the Job table. To do this, I believed I needed to use a lookup on the Employees table that would "look" to find a position for each employee, and create the link. Is this generally correct?
The problem I'm encountering now is that my sponsor does not want to bring in the full list of Jobs into the Job table. Yet, they want the "Job" field in the Employee table to list their current position, whether or not it is part of the curated list of jobs in the Jobs table. There is much resistance to bringing in all the jobs into the jobs table and creating the link like i described above, so aren't I hooped?
What they want to do (although understanding the implications) would be to have 4 job fields for each candidate.
1) Their current ACTUAL job (not a lookup)
2) #1 position they are being groomed for (lookup to Jobs table)
3) #2 position they are being groomed for (lookup to Jobs table)
4) #3 position they are being groomed for (lookup to Jobs table)
Last edited:
Hi again. I have had another request, would adding another table be the way to go?
Reminder of Setup (simplified)
Hey - Hope you're having a good weekend! Did you get the WoT credit I sent ya? Wasn't sure if you needed gold or premium time more, so went with the latter for this one.
If you have time, I'm wondering how you would approach a problem that has arisen.
Just to refresh, here is what we have for tables, and I'll simplify the data within to only the critical fields for our discussion.
Table1: Positions *these only contain the positions they want to look at right now
PositionID (key field)
PositionTitle
PositionIncumbent
PositionCompetency1 (these are used to put in the score this position requires for this particular competency, on a scale of 1-4).
PositionCompetency2
etc.
Table2: Candidates
CandidateID (key field)
CandidatePersonnel#
CandidateLastName
CandidateFirstName
CandidateCurrentPosition *Not a lookup, as the current position could be a job not included in the Table1 list
CandidateSeniorManager *Lookup to Table4
CandidateExecutive *Lookup to Table5
CandidateIdentifiedPosition1 *Lookup to Table1
CandidateIdentifiedPosition1Comment
CandidateIdentifiedPosition2 *lookup to Table1
CandidateIdentifiedPosition2Comment
CandidateIdentifiedPosition3 *lookup to Table1
CandidateIdentifiedPosition3Comment
etc.
Table3: Assessments
This table used to gather reviews on a candidate
AssessmentID (key field)
AssessmentName *Lookup to Table2
CandidateCompetency1Score *score 1-4
etc.
The general idea is to take all reviews for a particular individual, average or weighted average them and spit out a) an overall score which can be used to rank candidates against each other b) a gap analysis which shows how each candidate is doing against each competency 1-10. The first part I'm more comfortable with (although they are talking about being able to set weightings PER candidate, which I was against because this will be very difficult to program into the system), second part should be doable too.
Here is the problem: they want to eventually let the system spit out reports they will use, but they want to get there gradually, and maintain final control on ranking candidates for now (I dislike this idea but not my issue). The client wants some way to be able to enter, manually, what the candidate "field" looks like for each position. I don't want this to interfere with the existing system (ie. the impartial reporting of top candidates), so they have to be able to co-exist. From what I can gather, they want something that allows them to see something like this:
Position Name
Candidate#1
Candidate#2
Candidate#3
Candidate#4
Candidate#5
Candidate#6
Candidate#7
Candidate#8
Candidate#9
Candidate#10
Client wants this to be part of our solution so it doesn't have to be manually maintained in a separate SP list. Because it's manual entry and the system won't be generating this as a report using their scores (which was my intention) this needs to be a table, as far as I can gather. However, how do we tie this to existing tables???? Position Name would be a lookup surely, but is there a way that Access could allow the client to have a drop down selection for each candidate slot which would ONLY show the candidates IF, under Table#2, they had been associated with that positions as one of their three identified positions? Eg. Joe's profile in Table 2 shows:
Joe
Identified Position 1: CEO
Identified Position 2: CIO
Identified Position 3: COO
The new table I am proposing (if this is the way to go), for the CEO position, would allow you to slot Joe into one of those 10 slots, but not into the Janitor's 10 spots because he was not identified to that position. Would this be some sort of lookup with a criteria specified to filter out bad candidates?
Thoughts?
Thanks
Reminder of Setup (simplified)
Hey - Hope you're having a good weekend! Did you get the WoT credit I sent ya? Wasn't sure if you needed gold or premium time more, so went with the latter for this one.
If you have time, I'm wondering how you would approach a problem that has arisen.
Just to refresh, here is what we have for tables, and I'll simplify the data within to only the critical fields for our discussion.
Table1: Positions *these only contain the positions they want to look at right now
PositionID (key field)
PositionTitle
PositionIncumbent
PositionCompetency1 (these are used to put in the score this position requires for this particular competency, on a scale of 1-4).
PositionCompetency2
etc.
Table2: Candidates
CandidateID (key field)
CandidatePersonnel#
CandidateLastName
CandidateFirstName
CandidateCurrentPosition *Not a lookup, as the current position could be a job not included in the Table1 list
CandidateSeniorManager *Lookup to Table4
CandidateExecutive *Lookup to Table5
CandidateIdentifiedPosition1 *Lookup to Table1
CandidateIdentifiedPosition1Comment
CandidateIdentifiedPosition2 *lookup to Table1
CandidateIdentifiedPosition2Comment
CandidateIdentifiedPosition3 *lookup to Table1
CandidateIdentifiedPosition3Comment
etc.
Table3: Assessments
This table used to gather reviews on a candidate
AssessmentID (key field)
AssessmentName *Lookup to Table2
CandidateCompetency1Score *score 1-4
etc.
The general idea is to take all reviews for a particular individual, average or weighted average them and spit out a) an overall score which can be used to rank candidates against each other b) a gap analysis which shows how each candidate is doing against each competency 1-10. The first part I'm more comfortable with (although they are talking about being able to set weightings PER candidate, which I was against because this will be very difficult to program into the system), second part should be doable too.
Here is the problem: they want to eventually let the system spit out reports they will use, but they want to get there gradually, and maintain final control on ranking candidates for now (I dislike this idea but not my issue). The client wants some way to be able to enter, manually, what the candidate "field" looks like for each position. I don't want this to interfere with the existing system (ie. the impartial reporting of top candidates), so they have to be able to co-exist. From what I can gather, they want something that allows them to see something like this:
Position Name
Candidate#1
Candidate#2
Candidate#3
Candidate#4
Candidate#5
Candidate#6
Candidate#7
Candidate#8
Candidate#9
Candidate#10
Client wants this to be part of our solution so it doesn't have to be manually maintained in a separate SP list. Because it's manual entry and the system won't be generating this as a report using their scores (which was my intention) this needs to be a table, as far as I can gather. However, how do we tie this to existing tables???? Position Name would be a lookup surely, but is there a way that Access could allow the client to have a drop down selection for each candidate slot which would ONLY show the candidates IF, under Table#2, they had been associated with that positions as one of their three identified positions? Eg. Joe's profile in Table 2 shows:
Joe
Identified Position 1: CEO
Identified Position 2: CIO
Identified Position 3: COO
The new table I am proposing (if this is the way to go), for the CEO position, would allow you to slot Joe into one of those 10 slots, but not into the Janitor's 10 spots because he was not identified to that position. Would this be some sort of lookup with a criteria specified to filter out bad candidates?
Thoughts?
Thanks
I don't know what to tell you, mostly becuase I don't fully understand where you are trying to go. I do know the structure you posted isn't normalized and I would advise fixing that.
You shouldn't have numerated field names (Position1, Position2, Competency1, Compentency2 etc...). When you start doing that its time for a new table for all similiarly numerated fields.
Again, don't fully understand what you are trying to achieve. But if you want to limit the positions applicable to a person, you would need a new table to do that:
ApplicablePositions
ap_ID, autonumber, primary key
ID_Employee, number, foreign key to Employees
Position_ID, number, foreign key to Positions
Then you could use that with the first query we worked on (to score employees per position) to limit it to just the positions that are applicable to employees.
You shouldn't have numerated field names (Position1, Position2, Competency1, Compentency2 etc...). When you start doing that its time for a new table for all similiarly numerated fields.
Again, don't fully understand what you are trying to achieve. But if you want to limit the positions applicable to a person, you would need a new table to do that:
ApplicablePositions
ap_ID, autonumber, primary key
ID_Employee, number, foreign key to Employees
Position_ID, number, foreign key to Positions
Then you could use that with the first query we worked on (to score employees per position) to limit it to just the positions that are applicable to employees.
Hi Plog,
Just so I am understanding, would the recommendation be to have additional tables for the base data as follows:
Table1: Positions
Divide into way more (?) tables:
1a. Contains positions info
1b. Competency1 table (contains Position field, baseline required score field)
1c. Competency2 table
1c. Competency3 table
1d. Competency4 table
1e. Competency5 table
1f. Competency5 table
1g. Competency6 table
1h. Competency7 table
1i. Competency8 table
1j. Competency9 table
1k. Competency10 table
Table2: Candidates
Divide into way more (?) tables:
1a. Candidate info
1b. IdentifiedPosition1 (contains Position lookup field, candidate lookup field)
1c. IdentifiedPosition2
1d. IdentifiedPosition3
Table3: Assessment
Similar structure, one table per asessment PER competency? I can't get this to work with my workflow in SharePoint easily.
Just so I am understanding, would the recommendation be to have additional tables for the base data as follows:
Table1: Positions
Divide into way more (?) tables:
1a. Contains positions info
1b. Competency1 table (contains Position field, baseline required score field)
1c. Competency2 table
1c. Competency3 table
1d. Competency4 table
1e. Competency5 table
1f. Competency5 table
1g. Competency6 table
1h. Competency7 table
1i. Competency8 table
1j. Competency9 table
1k. Competency10 table
Table2: Candidates
Divide into way more (?) tables:
1a. Candidate info
1b. IdentifiedPosition1 (contains Position lookup field, candidate lookup field)
1c. IdentifiedPosition2
1d. IdentifiedPosition3
Table3: Assessment
Similar structure, one table per asessment PER competency? I can't get this to work with my workflow in SharePoint easily.
That's not how you fix numerated fields, you don't then numerate tables. Suppose you had this:
EmplID, IdentifiedPosition1, IdentifiedPosition2, IdentfiedPosition3
17, CEO, Janitor, Sales
14, Janitor, IT Director, Driver
13, Driver, Sales, Janitor
The correct way to structure that data is 1 table like so:
EmpID, IdentifiedPosition
17, CEO
17, Janitor
17, Sales
14, Janitor
14, IT Director
...
If the suffixed number (Position1, Position2, etc.) was important, like a priority, you would create an additional field in the above table to store it:
EmpId, IdentifiedPosition, Priority
17, CEO, 1
17, Janitor, 2
17, Sales, 3
...
You fix it with more rows, not with more tables or fields.
EmplID, IdentifiedPosition1, IdentifiedPosition2, IdentfiedPosition3
17, CEO, Janitor, Sales
14, Janitor, IT Director, Driver
13, Driver, Sales, Janitor
The correct way to structure that data is 1 table like so:
EmpID, IdentifiedPosition
17, CEO
17, Janitor
17, Sales
14, Janitor
14, IT Director
...
If the suffixed number (Position1, Position2, etc.) was important, like a priority, you would create an additional field in the above table to store it:
EmpId, IdentifiedPosition, Priority
17, CEO, 1
17, Janitor, 2
17, Sales, 3
...
You fix it with more rows, not with more tables or fields.
I am starting to understand - thank you for your patience.
Two followups:
a) data entry side. In access, on teh data entry side, i assume we would use forms to tie this all together somehow, but I'm unclear as to how the forms would then write back data to all these separate tables.
b) query side: We had queries working before with a structure that allowed one table to store assessments where the table looked like
EmpID, Com1Score, Comp2Score etc.
As far as I know from a SharePoint workflow side, which i'm using to gather these, that format would need to stand for my review process to work.
What I mean is my SharePoint form for assessing a person on each of the 10 competencies allows entry into a single list item (a single row in Access). I need to keep it this way for the assessments, could I do that somehow?
Two followups:
a) data entry side. In access, on teh data entry side, i assume we would use forms to tie this all together somehow, but I'm unclear as to how the forms would then write back data to all these separate tables.
b) query side: We had queries working before with a structure that allowed one table to store assessments where the table looked like
EmpID, Com1Score, Comp2Score etc.
As far as I know from a SharePoint workflow side, which i'm using to gather these, that format would need to stand for my review process to work.
What I mean is my SharePoint form for assessing a person on each of the 10 competencies allows entry into a single list item (a single row in Access). I need to keep it this way for the assessments, could I do that somehow?
Last edited:
a. Forms that directly interact with data (add/edit/delete) should be bound to a table. You simply set the Record Source property of the form to the appropiate table.
b. You can create queries like that. You wouldn't store your data like that.
Creating a database should go as follows: Set up the proper structure (tables), set up output (reports/queries), set up input (forms).
Proper structuring should influence your output and input, but not the other way around. How you want your reports and forms to operate has little influence on how you structure your tables, other than letting you know what data fields need to be accomodated. Proper normalization is really independent of anything else downstream. Work on your tables, then worrry about reports/forms.
b. You can create queries like that. You wouldn't store your data like that.
Creating a database should go as follows: Set up the proper structure (tables), set up output (reports/queries), set up input (forms).
Proper structuring should influence your output and input, but not the other way around. How you want your reports and forms to operate has little influence on how you structure your tables, other than letting you know what data fields need to be accomodated. Proper normalization is really independent of anything else downstream. Work on your tables, then worrry about reports/forms.
Hi again Plog, I need your help.
I took your advice and split off some of the tables. I'm trying to get a lookup to work, and I think it should "just work" if the relationships are correct, but maybe I'm wrong?
Tables concerned are:
-Candidate Table
-Candidate Identified Table (essentially an eligibility table)
-Positions Table
What I want to do is this:
Have another table with two lookups
A - First lookup allows user to pull down a Position
B - Second lookup allows user to pull down a name of a person, but this list should dynamically filter down to only those individuals who are attached to position (from A) in the eligibility table.
I took your advice and split off some of the tables. I'm trying to get a lookup to work, and I think it should "just work" if the relationships are correct, but maybe I'm wrong?
Tables concerned are:
-Candidate Table
-Candidate Identified Table (essentially an eligibility table)
-Positions Table
What I want to do is this:
Have another table with two lookups
A - First lookup allows user to pull down a Position
B - Second lookup allows user to pull down a name of a person, but this list should dynamically filter down to only those individuals who are attached to position (from A) in the eligibility table.
This is a form issue, and that's not really my forte. Specifically what you are talking about is a 'cascading combobox' or 'cascading dropdown'. Search the forum for those terms--they have been solved many times.
Hi Plog,
All my data is in and looking at queries again.
I am building queries and was thinking of posting here as I work through them.
First one is currently as such, it averages all scores. I would like it, however, to ignore values of 0 in the averaging if they are present.
How would you do this?
Also - not understanding the SQL statements with "AS ... ". I changed the captions for the columns but did so in Design View.
All my data is in and looking at queries again.
I am building queries and was thinking of posting here as I work through them.
First one is currently as such, it averages all scores. I would like it, however, to ignore values of 0 in the averaging if they are present.
How would you do this?
Also - not understanding the SQL statements with "AS ... ". I changed the captions for the columns but did so in Design View.
Code:
SELECT Assessments.AssessmentEmployeeName, Avg(Assessments.AssessmentLCMExecution) AS AvgOfAssessmentLCMExecution, Avg(Assessments.AssessmentLCMLeadership) AS AvgOfAssessmentLCMLeadership, Avg(Assessments.AssessmentLCMEngage) AS AvgOfAssessmentLCMEngage, Avg(Assessments.AssessmentLCMTalent) AS AvgOfAssessmentLCMTalent, Avg(Assessments.AssessmentLCMRelationships) AS AvgOfAssessmentLCMRelationships, Avg(Assessments.AssessmentLCMChange) AS AvgOfAssessmentLCMChange, Avg(Assessments.AssessmentLCMBusiness) AS AvgOfAssessmentLCMBusiness, Avg(Assessments.AssessmentLCMDecisions) AS AvgOfAssessmentLCMDecisions, Avg(Assessments.AssessmentLCMFinancial) AS AvgOfAssessmentLCMFinancial, Avg(Assessments.AssessmentLCMShareholder) AS AvgOfAssessmentLCMShareholder
FROM Assessments
GROUP BY Assessments.AssessmentEmployeeName;To eliminate 0 values you would add a WHERE clause:
WHERE AssessmentLCMExecution>0
AS in the SELECT clause is how you rename a field. Whatever you put after the AS is the name that will show in the column heading of that field.
All queries are SQL strings (text). Design view is just a graphical user interface to make it easier to construct that SQL string. Its so good in fact, most people don't realize they are actually generating an SQL string. But that's what is happening. So when you do this:
ThisIsTheNameField: AssessmentEmployeeName
In Design View, what it does is add the AS to the SQL string. You can always View SQL from design view, but sometimes can't get into design view from SQL because SQL can be so complex Design View can't comprehend it.
WHERE AssessmentLCMExecution>0
AS in the SELECT clause is how you rename a field. Whatever you put after the AS is the name that will show in the column heading of that field.
All queries are SQL strings (text). Design view is just a graphical user interface to make it easier to construct that SQL string. Its so good in fact, most people don't realize they are actually generating an SQL string. But that's what is happening. So when you do this:
ThisIsTheNameField: AssessmentEmployeeName
In Design View, what it does is add the AS to the SQL string. You can always View SQL from design view, but sometimes can't get into design view from SQL because SQL can be so complex Design View can't comprehend it.
I know for a fact if you go into SQL view and paste it, it will work. In design view, you will have to bring in AssessmentLCMExecution again and underneath it, in the Totals area, set it to WHERE and then add your criteria. You cannot just add it underneath the existing field you are averaging.
Ah ok. I'll try that first thing tomorrow! Thank you!