Meeting Several Conditions in a Single Field

[Vyurr]

Good morning all,

The query I am trying to create is a doozy and I could really use some assistance.

Background

Jobs in my office have a list of competency levels that an employee must achieve through testing in order to apply for a position. The goal of this query is to provide a count of employees that have met ALL of the required competency levels for a particular position. (I can make it count employees that have met the required level and list them by competency, what I need is a single number that takes into account and counts only employees who have met ALL competency levels for a job)

Tables

tblCompetencies

CompID
CompetencyName


tblJobNumbers

JobID
JobName
JobNumber

tblJobLevelReq

JobID
CompID
LevelReq

tblEmployees

EmpID
FirstName
LastName
JobID

tblEmployeeLevel

EmpID
CompID
LevelAttained


From speaking to some of the "Access Gurus" in our office, I understand that such a query is complex, but as far as I can tell my data is fully normalized so I know it must be possible. Any assistance would be greatly appreciated.

V

 

[BigRed1212]

Is there more than one competency required for a given job and is a particular level of any given competency required?

 

[Vyurr]

Yes, each job has several competencies required at varying levels. I can make the comparison [Level]>=[LevelReq] for each competency and can count the number of [EmpID]s that meet that criteria, by competency. What I need are only the results where [Level]>=[LevelReq] for ALL of the competencies of a given job.

 

[lespaul]

so you want to find all the competencies for a job and then find all the employees that have all the competencies (by meeting or exceeding the LevelReq?) and return the number of employees?




Leslie

 

[Vyurr]

Yes and no

The tblJobLevelReq already contains all of the levels for each job, by competency. ([LevelReq], [JobID], [CompID])

What I need is the number of employees that meet ALL of the competency levels for a given job. I can pull the # of employees that meet each individual competency, but can't seem to pull if there are any employees that meet ALL of them.

Thanks again,

V

 

[MichaelRed]

briefly, the generic industry term is KSA, standing for "Knowledge, skill and abillity". It would help you to research the topic.

In your table Job Level Req, you include the field LevelReq. It is, however not shown in any other table. I believe it needs to tblJobs and tblCompetency and / or tblEmployeeLevel.



MichaelRed

 

[Vyurr]

I think, (and I could be wrong) that the LevelReq field is ok in just the tblJobLevelReq because there are relationships between tblJobLevelReq and the other tables you mentioned. I'm pretty sure, to keep the data normalized, I need to keep those seperate...again, I could be wrong.

Thanks for the response

V

 

[Vyurr]

Thank you everyone for your thoughts...my problem has been solved. For those who are interested, I used a double-negation method to return the results I was looking for:

The "double-negation" method use the logic of 2 negations:

1. First negation: Find the Employees that do not meet the specific job's competency requirements.

2. Second negation: Find Employees (from [tblEmployees]) that are not in the list returned by the first negation which should leave you with only Employees that have all the required competencies + min levels for this particular Job.

V