Comparison of Candidates against a given Position (1 Viewer)

[diofree]

So for instance, in SQL view
I would do this?
(first line)

SELECT CandidateIdentified.CandidateIdentifiedCandidate, Assessments.AssessmentType, Avg(Assessments.AssessmentLCMExecution) AS AvgExecution WHERE AssessmentLCMExecution>0 ...

 

[plog]

Not exactly. SQL has a specific structure of clauses:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

So a WHERE clause goes after the FROM clause and before the GROUP BY clause, not after the SELECT clause.

 

[diofree]

Ok got it,
So I tried this but I guess i can't do commas with a WHERE...

SELECT CandidateIdentified.CandidateIdentifiedCandidate, Assessments.AssessmentType, Avg(Assessments.AssessmentLCMExecution) AS AvgExecution, Avg(Assessments.AssessmentLCMLeadership) AS AvgLeadership, Avg(Assessments.AssessmentLCMEngage) AS AvgEngage, Avg(Assessments.AssessmentLCMTalent) AS AvgTalent, Avg(Assessments.AssessmentLCMRelationships) AS AvgRelationships, Avg(Assessments.AssessmentLCMChange) AS AvgChange, Avg(Assessments.AssessmentLCMBusiness) AS AvgBusiness, Avg(Assessments.AssessmentLCMDecisions) AS AvgDecisions, Avg(Assessments.AssessmentLCMFinancial) AS AvgFinancial, Avg(Assessments.AssessmentLCMShareholder) AS AvgShareholder
FROM ((Candidates INNER JOIN Assessments ON Candidates.ID = Assessments.AssessmentEmployeeName) INNER JOIN (Positions INNER JOIN CandidateIdentified ON Positions.PositionID = CandidateIdentified.CandidateIdentifiedPosition) ON Candidates.ID = CandidateIdentified.CandidateIdentifiedCandidate) INNER JOIN (Competencies INNER JOIN PositionBaselines ON Competencies.CompentenciesID = PositionBaselines.PositionBaselinesCompetency) ON Positions.PositionID = PositionBaselines.PositionBaselinesPosition
WHERE Assessments.AssessmentLCMExecution>0, Assessments.AssessmentLCMLeadership>0, Assessments.AssessmentLCMEngage>0, Assessments.AssessmentLCMTalent>0, Assessments.AssessmentLCMRelationships>0, Assessments.AssessmentLCMChange>0, Assessments.AssessmentLCMBusiness>0, Assessments.AssessmentLCMDecisions>0,   Assessments.AssessmentLCMFinancial>0, Assessments.AssessmentLCMShareholder>0
GROUP BY CandidateIdentified.CandidateIdentifiedCandidate, Assessments.AssessmentType;

 

[diofree]

Also, I have a sense you won't like this, but any suggestions on converting the query/report from what I'm getting to what the business wants? See attached diagram.

If i can get it into that format, I can the gap column working I believe.

 

[plog]

To seperate parts of the WHERE clause you use AND or OR keywords:

Criterion1='ABCD' AND Criterion2=7 AND ...


And I don't fully understand the format you are trying to achieve. Possibly run your query to get it to Excel then format it there via a pivot table.

 

[diofree]

To seperate parts of the WHERE clause you use AND or OR keywords:

Criterion1='ABCD' AND Criterion2=7 AND ...

Arg, I'm so sorry. To summarize:

So if I want each column's AVG field

eg. Avg(Assessments.AssessmentLCMExecution)

To only average values that are between 1-4.

I would do so by adding a WHERE after the GROUP BY, which lists every field like so:

WHERE Assessments.AssessmentLCMExecution>0

With an AND in between each field?

Will this do what I want, (ie. ignore zeroes in averaging) or will it not even run averages if there is a zero present?

 

[plog]

Now your in trouble with your improper structure. You shouldn't store data in field names. LCMExecution should be a value in a field, not the name of a field. Then you could use the AVG function of SQL along with a WHERE clause to get what you want.

I don't know what to tell you. With your set up its not possible to simply use a WHERE clause or the AVG function. If you use a WHERE clause to exclude records with LCMExecution=0 you exclude the whole record and not just that one field's value.

That means if it has a LCMLeadership value it will be excluded because the LCMExecution value is 0. This is why you need the proper structure to your tables.

 

[diofree]

Ok got it.
The assessment piece is coming out of sharepoint and I saw no other way to do it, so that one piece does have an improper structure, no doubt.

The reason '0' was added as allowable was to give the user a null value. Is there any other way to do this other than having the user leave the field blank - which I assume would get me to what i'm after?

 

[plog]

I actually think NULL would be handled properly.

AVG(NULL, NULL, 4, 8, ) = 6

AVG(0, 0, 4, 8) = 3

 

[diofree]

Ok - so very rookie question:

Null = "" but are you saying Null is also recognized when a user enters "Null" ?

Right now the score fields are number fields because i thought that would be the most versatile to do math on later on in the queries.

 

[plog]

NULL is the mathematical empty set--the absence of data.

So, "NULL" isn't NULL, "" (empty string) isn't NULL. NULL is NULL--no data at all. The field type doesn't matter--all can accomodate NULL.

 

[diofree]

Ok thanks for clarifying for me!