Search Function - Join a look up table with multiple rows and where clause

Question

I am building a search function with Microsoft Access 2013 and VBA. My database has three tables, tblCandidate, tblCandidateSkill, lutSkill, although lutSkill isn't really necessary for this discussion, I thought it might beneficial to share anyways.

Below are my table definitions:

tblCandidate              tblCandidateSkill           lutSkill
----------------          -----------------           -------------
CandidateId               CandidateSkillId            SkillId          
FirstName                 CandidateId                 Name
MiddleName                SkillId                     Description
LastName                  YearsExp
etc...                 

A candidate can have multiple skills which each record in tblCandidateSkill relates to a single record in lutSkill. I've created a query/view (viewCandidate) which joins tblCandidate and tblCandidateSkill. This works as expected. If a Candidate has several skills, each skill is listed in a separate row along with the candidates information.

My search function in VBA is basically just a bunch of nested If Else statements that generate some dynamic SQL.

Here is my code below for the curious.

Private Sub Search_Click()

' Declare variables
Dim stateId As Integer
Dim skillId1 As Integer
Dim skillId2 As Integer
Dim skillId3 As Integer
Dim yearsExp1 As Integer
Dim yearsExp2 As Integer
Dim yearsExp3 As Integer

Dim count As Integer

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Dim strQuery As String
Dim strWhere As String
Dim strSQL As String

On Error GoTo errHandler

strQuery = "qryCandidateSearchResults"
strSQL = "SELECT FirstName, MiddleName, LastName, Phone, Email FROM viewCandidate WHERE "

'===========================================================
' Start dynamic query

' Check for state id
If Not IsNull(Me.ddlState.Value) Then
    strWhere = strWhere & " ([StateId] = " & Me.ddlState.Column(0) & ") AND "
End If

' Check for skillId1 and yearsExp1
If Not IsNull(Me.ddlSkill1.Value) Then

    ' if yearsExp1 is not null, include it,
    ' otherwise dont.
    If Not IsNull(Me.ddlYearsExp1.Value) Then
        strWhere = strWhere & " (SkillId = " & Me.ddlSkill1.Column(0) & " AND YearsExp >= " & Me.ddlYearsExp1.Column(0) & ") AND "
    Else
        strWhere = strWhere & " (SkillId = " & Me.ddlSkill1.Column(0) & ") AND "
    End If
End If

' Check for skillId2 and yearsExp2
If Not IsNull(Me.ddlSkill2.Value) Then

    If Not IsNull(Me.ddlYearsExp2.Value) Then
        strWhere = strWhere & " (SkillId = " & Me.ddlSkill2.Column(0) & " AND YearsExp >= " & Me.ddlYearsExp2.Column(0) & ") AND "
    Else
        strWhere = strWhere & " (SkillId = " & Me.ddlSkill2.Column(0) & ") AND "
    End If
End If

' Check for skillId3 and yearsExp4
If Not IsNull(Me.ddlSkill3.Value) Then

    If Not IsNull(Me.ddlYearsExp3.Value) Then
        strWhere = strWhere & " (SkillId = " & Me.ddlSkill3.Column(0) & " AND YearsExp >= " & Me.ddlYearsExp3.Column(0) & ") AND "
    Else
        strWhere = strWhere & " (SkillId = " & Me.ddlSkill3.Column(0) & ") AND "
    End If
End If


'============================================================

' Remove the last "AND"
If Right(strWhere, 4) = "AND " Then
    strWhere = Mid(strWhere, 1, Len(strWhere) - 4)
End If

strSQL = strSQL & strWhere

' Check for criteria and exit if none exists
' else set CurrentDb, QueryDef and SQL string
If Len(strWhere) = 0 Then
    MsgBox "You didn't enter any criteria for this report, now exiting", vbExclamation
    Exit Sub
Else
    Set db = CurrentDb
    Set qdf = db.QueryDefs(strQuery)
    qdf.SQL = strSQL
End If

DoCmd.OpenReport "rptCandidateSearch", acViewPreview
Debug.Print strSQL


Exit Sub

errHandler:
Select Case Err.Number
    Case 2501
        'No data
        Resume Next
    Case Else
        MsgBox Err.Number & " - " & Err.Description
End Select

End Sub

My problem stems for each skill being on a separate row. For example, if I want to search the database for a candidate that has three skills, how can I filter that data to show only those candidates that match the search criteria? All my attempts have failed since each skillId is listed on a separate row.. If I use And in the where clause, nothing catches, and if I use OR the results are inaccurate.

Answer 1

As far as the SQL is involved I would thing you would want to do something like:

Select CandidateId, Count(*)
from viewCandidate
where CandidateSkillId in (skill1,skill2,skill3)
having count(*) = skillCt