Return results that exactly match criteria

[gungrave19]

I have 3 main tables: tblEmployees, tblJobs, and tblProcedures. (See attachment for relationship diagram and additional supplemental tables)

A job can have multiple procedures and an employee can have multiple procedures too.

I need to write a query such that when searching by a specific job I can see all of the employees who are qualified for that job. This is done by seeing which employees have the procedures that belong to a job. But here's the catch: since a job can have multiple procedures, if an employee only has some of the procedures I don't want that particular employee to return as a search result. The employee must have ALL the procedures that belong to the selected job.

So for instance if I have:

tblJobs
Job1

tblEmployees
Emloyee1
Employee2

tblProcedures
Procedure1
Procedure2

Job1 has Procedure1 and Procedure2
Employee1 only has Procedure1
Employee2 has both Procedure1 and Procedure2

If I search by Job1, I want only Employee2 to return as a result, NOT Employee1.

I am at a lost for how to construct the SQL for something like that. Can anyone point me to an article on how to do this or give me a general structure for this type of query?

Thanks in advance for any help!

 

[arichins]

I can think of a way to do this with derived tables in a query, or with using code to loop through a record set...I like to use queries whenever possible though.

So you'd make a derived table that has the JobID, procedure_IDs for the job in question, and the count of total procedures needed for that job.

Then you make a derived table that has the employeeID,procedureID they are qualified for that pertain to the job in question, and a count of those rows. Then you could join those tables on the count rows.

So it would basically be like saying: Job A requires 5 procedures. These Employees have this many procedures pertaining to that job. Any employees that have 5 procedures pertaining to that job are qualified.


Or you could load the needed procedures into a record set and write code to loop through that record set for each employee, but I think it could be done with a query.

 

[billmeye]

I have done something similar and quickly modified my VBA with your tables:

Function CheckProcs(EmplID As Long, JobID As Long) As String
Dim Rs As DAO.Recordset, MySql As String
Dim HasCount As Integer, NoCount As Integer

MySql = "SELECT * FROM tblJob_Procedure_INT WHERE ((([JobFK])=" & JobID & "));"
Set Rs = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
Rs.MoveFirst
Do While Not Rs.EOF
If DCount("*", "tblEmployee_Procedure_INT", "[EmployeeFK]=" & Empl & " AND [ProcedureFK]=" & Rs!ProcedureFK) > 0 Then
HasCount = HasCount + 1
Else
NoCount = NoCount + 1
End If
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
If HasCount = HasCount + NoCount Then CheckProcs = "Yes" Else CheckProcs = "No"
End Function

You would have a query based on your tblEmployees with the added field Expr1:CheckProcs([Employee_ID],[Forms]![YourForm]![JobSelectComboBox]) with criteria set to "Yes".

The result should be just Employees who have all the required Procedures.

 

[gungrave19]

Thanks to both of you for replying. I really appreciate you guys giving your time to help me out.

I ended up using billmeye's code with a few modifications, mainly because it was throwing an error when there was no record in tblJob_Procedure_JNT that matched a selected job. The parts I changed/added are in red:

Function CheckProcs(EmplID As Long, JobID As Long) As String
Dim Rs As DAO.Recordset, MySql As String
Dim HasCount As Integer, NoCount As Integer
MySql = "SELECT * FROM tblJob_Procedure_JNT WHERE ((([JobFK])=" & JobID & "));"
Set Rs = CurrentDb.OpenRecordset(MySql, dbOpenSnapshot)
If Rs.EOF = True And Rs.BOF = True Then
Exit Function
Else
Rs.MoveFirst
End If
Do While Not Rs.EOF
If DCount("*", "tblEmployee_Procedure_JNT", "[EmployeeFK]=" & EmplID & " AND [ProcedureFK]=" & Rs!ProcedureFK) > 0 Then
HasCount = HasCount + 1
Else
NoCount = NoCount + 1
End If
Rs.MoveNext
Loop
Rs.Close
Set Rs = Nothing
If HasCount = HasCount + NoCount Then CheckProcs = "Yes" Else CheckProcs = "No"
End Function

It's working great. I never would have been able to figure this out on my own. Thanks again!

 

[billmeye]

I'm glad it worked for you.