B
Bam Bam
I have a recruitment agency database that holds records on
Candidates(prospective employees), Clients(prospective employers) and
individual Job Specifications(Many per Client). I'm attempting to build a
search form that returns a results list of Suitable Candidates to utilize as
a Shortlist of prospective employees to put forward for interviews. The
search form has controls for the criterion to be searched, the resultant
records appear in a datasheet subform. The search form works nicely to
return Candidates based on the first three fields in each of the two lists
below(using a simple SELECT statement).
Here is a cut-down of the fields in the search form(which are passed from
the Job Spec requirements but
are searched against the tblCandidates fields):
-TypingMinimum(WPM)
-MSWord(Yes/No)
-MSExcel(Yes/No)
-PositionTitle one: drawn from the Job Spec
-ProfessionalCriteria many: drawn from the Job Spec
Here is a cut-down of the Candidate fields to be searched against:
-Typing(WPM) in tblCandidates
-MSWord(Yes/No) in tblCandidates
-MSExcel(Yes/No) in tblCandidates
-PositionTypesSought many in own table linked to tblCandidates by
CandidateID
(e.g Reception, Administration, Secretary)
Equates to PositionTitle on search form
-ProfessionalCriteria many in own table linked to tblCandidates by
CandidateID
(e.g Innitiative, Legal experience, Ability to multi-task)
My Problem:
a) The PositionTitle on the search form is a single value and needs to
compare against each PositionTypesSought for a Candidate to find if at least
one of them is equal, in which case a TRUE would somehow be returned to show
that the Candidate is looking for that kind of work.
b) The ProfessionalCriteria on the search form are many values and one by
one, each needs to be compared against each ProfessionalCriteria on the
Candidate side. As the search iterates with each search side value in turn
against each Candidate side value, only ONE match would be needed in all the
cycles to somehow return a TRUE. (I guess the implied business rule here is
that a Candidate only needs to possess one of the required
ProfessionalCriteria to come-up on the search - I'm happy with this for the
moment as the search fields mentioned above (and others) help to narrow down
the search anyway). This one makes my head spin like The Exorcist girl.
I hope this makes any kind of sense, the thing with a) and b) is that
although I THINK I can define the problem and my VBA skills are creeping
into the intermediate level, I have no idea of how to manage the recordsets
required to pull it off and then meld the result of a) and b) into the final
SELECT statement.
Any help or pointers towards useful examples will be greatly appreciated.
Candidates(prospective employees), Clients(prospective employers) and
individual Job Specifications(Many per Client). I'm attempting to build a
search form that returns a results list of Suitable Candidates to utilize as
a Shortlist of prospective employees to put forward for interviews. The
search form has controls for the criterion to be searched, the resultant
records appear in a datasheet subform. The search form works nicely to
return Candidates based on the first three fields in each of the two lists
below(using a simple SELECT statement).
Here is a cut-down of the fields in the search form(which are passed from
the Job Spec requirements but
are searched against the tblCandidates fields):
-TypingMinimum(WPM)
-MSWord(Yes/No)
-MSExcel(Yes/No)
-PositionTitle one: drawn from the Job Spec
-ProfessionalCriteria many: drawn from the Job Spec
Here is a cut-down of the Candidate fields to be searched against:
-Typing(WPM) in tblCandidates
-MSWord(Yes/No) in tblCandidates
-MSExcel(Yes/No) in tblCandidates
-PositionTypesSought many in own table linked to tblCandidates by
CandidateID
(e.g Reception, Administration, Secretary)
Equates to PositionTitle on search form
-ProfessionalCriteria many in own table linked to tblCandidates by
CandidateID
(e.g Innitiative, Legal experience, Ability to multi-task)
My Problem:
a) The PositionTitle on the search form is a single value and needs to
compare against each PositionTypesSought for a Candidate to find if at least
one of them is equal, in which case a TRUE would somehow be returned to show
that the Candidate is looking for that kind of work.
b) The ProfessionalCriteria on the search form are many values and one by
one, each needs to be compared against each ProfessionalCriteria on the
Candidate side. As the search iterates with each search side value in turn
against each Candidate side value, only ONE match would be needed in all the
cycles to somehow return a TRUE. (I guess the implied business rule here is
that a Candidate only needs to possess one of the required
ProfessionalCriteria to come-up on the search - I'm happy with this for the
moment as the search fields mentioned above (and others) help to narrow down
the search anyway). This one makes my head spin like The Exorcist girl.
I hope this makes any kind of sense, the thing with a) and b) is that
although I THINK I can define the problem and my VBA skills are creeping
into the intermediate level, I have no idea of how to manage the recordsets
required to pull it off and then meld the result of a) and b) into the final
SELECT statement.
Any help or pointers towards useful examples will be greatly appreciated.