Looking for Sql code for my Querry! 3

[NicuPetric]

I have 2 * "many to many" relations in my MS Access database.
1. Employee vs. KnowHow linked by linkEmpKnow
2. JobProfile vs. KnowHow linked by linkJobKnow
I would like to filter employees by a certain JobProfile(the employee should have at least all the knowhows the Job requires).

Thanks!!

 

[PHV]

Have you tried to build your query in the Query Design Window ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[NicuPetric]

Ok!
1. I have 5 tables in this relationship

2. How can I code the fact that all the KnowHows a Job requires must be met by the employee that matches the criteria.

 

[PHV]

Can you please post the SIMPLER sql code you have so far with some input examples values, given results vs expected results ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[NicuPetric]

Tables: (links between + + # # and - -.
1.tJobProfile
JobProfileID +
JobName
Details

2.tLinkProfileKnow (Activity Matrix)
JobProfileID +
KnowHowID #

3.tKnowHow (List of possible Know Hows)
KnowHowID # *
Name
Description

4.tLinkEmpKnow
KnowHowID *
EmployeeID -

5.tEmployees
EmployeeID
Name
Department
.
.
.
What I've done so far is:
If I select a JobProfile I obtain a query whit all the KnowHows this JobProfile requires.

Having this list of KnowHows I must find all the employees wich have entries to all of this KnowHows in the tLinkEmpKnow linking table.

 

[NicuPetric]

Tables: (links between + +, # #, * * and - -).
1.tJobProfile
JobProfileID +
JobName
Details

2.tLinkProfileKnow (Activity Matrix)
JobProfileID +
KnowHowID #

3.tKnowHow (List of possible Know Hows)
KnowHowID # *
Name
Description

4.tLinkEmpKnow
KnowHowID *
EmployeeID -

5.tEmployees
EmployeeID -
Name
Department
.
.
.
What I've done so far is:
If I select a JobProfile I obtain a query whit all the KnowHows this JobProfile requires.

Having this list of KnowHows I must find all the employees wich have entries to all of this KnowHows in the tLinkEmpKnow linking table.

 

[LittleSmudge]

I think what you need to do is add a

COUNT(JobProfileId) As KnowCount
and a
GROUP BY JobProfileId)

to the SELECT query that you already have


Then do a

SELECT Count(EmployeeId) As MatchCount , [other fields]
FROM Employee INNER JOIN linkEmpKnow
WHERE KnowHowId IN ( add above Select query here )
AND MatchCount = KnowCount
GROUP BY EmployeeId


The
WHERE KnowHowId IN ( add above Select query here )
will return all employees with at least one match
That's not exactly what you want so add the
Count(EmployeeId) and GROUP By
AND then just return records where MatchCount = the number of Knowledge cases you need.


You might need to do this in code if KnowCount isn't available as a specific value outside the brackets.



'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[r937]

this is tricky, because it involves a 5-way join, from one many-to-many (job profiles to knowhows) to another (knowhows to employees)

so for a moment, let's focus on just one -- knowhows to employees

the general strategy is to join each employee to all of her knowhows

select K.Name as KnowHow
     , E.Name as Employee
  from tKnowHow  as K
inner
  join tLinkEmpKnow  as EK
    on K.ID
     = EK.KnowHowID 
inner
  join tEmployees  as E
    on EK.EmployeeID
     = E.ID

once you ensure this join is working correctly, what you do is specify the desired knowhows in the WHERE clause, then group on the employee and count to see how many different knowhows she has

select E.Name as Employee
  from tKnowHow  as K
inner
  join tLinkEmpKnow  as EK
    on K.ID
     = EK.KnowHowID 
inner
  join tEmployees  as E
    on EK.EmployeeID
     = E.ID   
 where K.Name in ( 'HTML', 'CSS', 'SQL' )
group
    by E.Name
having count(distinct K.ID) = 3

the HAVING clause here requires that each employee has all three of the knowhows which were filtered in the WHERE clause

you could even say HAVING count(distinct K.ID) >= 2 which would find employees whith at least two of the selected knowhows

finally, to finish the join, you can drive the specification of which knowhows are filtered for, by joining the K table to the PK table and the PK table to the P table, and specifying the JobName in the WHERE clause





rudy

http://r937.com/

SQL Consulting

 

[NicuPetric]

Both solutions: LittleSmudge & r937 are fine, thanks boys. This was the solution I was thinking of but was not able to find the code.(my first access project). The ideea of using the count function to compare the number of required KHows is tricky. The structure I posted was a simplyfied one, in reality I have a "Level" field in the linking tables and my criteria is that the employee should have the aqquirred level >= than the needed one. I think I can do the changes alone.
Thanks!!