Match all records in 2 fields

[TKnight]

Hi, I'm trying to write an employment search engine which matches job descriptions to candidate skills. Vacancies and candidates each have multiple skills in fields [ESkill] and [Candidate_Skills] respectively. If I join the two fields in a query they match skills individually but i want to only find candidates who posess ALL the skills requested by the vacancy not just one. Can anyone point me in the right direction, thanks, Tom.

 

[Jack Cowley]

You say, "...each have multiple skills in fields [ESkill] and [Candidate_Skills] respectively". If your fields have something like 'Engine Repair, Wheel Alignment, Brakes' then you need to normalize your table and data. You should NOT have multiple data in a field.

hth,
Jack

 

[TKnight]

I've probaly misled you with my field names... both the fields contain the same data (Skills) it's just that someone searching for a candidate will add the skills required to do a job to a continuous form (or datasheet- whatever). If I then try and pull candidates which match the skills out using the field on the form it only searches for the single current record's skill. If I link the fields in a query it matches them individually which is no good because it will pull up candidates which posess any skill listed not ALL listed.

The only idea i have so far is to count the number of essential skills requested in the search. Then match skills individually and only return candidates which match the same number of skills that is requested. This seems like a long winded way of doing things though?
Thanks for your help, Tom.

 

[Jack Cowley]

I will assume that there is only ONE E_Skill and ONE Candidate_Skill record for each skill. I will also assume that if a candidate has 5 skills then there are 5 separate records, one for each skill. If all of that is true then how do you enter the data to search for 2 possible skills? Does the user enter some thing like, Teacher, Lecturer into a field to look for those to skills?

Give me a concrete example of what you are trying to do and the general setup of your tables of Candidates and their skills (should be two tables). It still sounds to me like your tables are not normalized because if they were it should not be that hard to search a single field in multiple records using multiple criteria.

hth,
Jack