How to select individuals with more than one skill

[scubadiver007]

Hello,

How do I select individuals who have, for example, both access and excel skills? I have a table of candidates and child table with the list of skills.

I have a form (called "search" at the moment) with a subform (called "skillslist subform"). The subform's record source is an empty table called "searchlist" with a single combo box using the master list of skills as the source. This is so I can select the skills I am looking for.

Then there is a button that (1) saves the records and then (2) opens the query, as follows:

SELECT SkillList.CandidateID, searchlist.searchlist
FROM SkillList INNER JOIN searchlist ON SkillList.Skill = searchlist.searchlist
ORDER BY SkillList.CandidateID;

Is this problem need to be solved with a subquery?

 

[plog]

Your trying to open forms, handle button clicks, write queries, deal with sub-tables. I have no idea what your real issue is. I think it has to do with a query that isn't producing the correct results, but you've muddied the issue with 5 other things that don't really seem relevant to your issue.

If you are trying to get a query to generate expected results, post sample data. Include table and field names from all relevant data sources, then based on that initial sample data post what data you expect your query to return. Use this format for posting data:

TableNameHere
Field1Name, Field2Name, Field3Name
David, 12/31/2013, 19
Steve, 12/8/2009, 41
Larry, 11/9/2009, 33

 

[scubadiver007]

I have no idea what your real issue is

The clue is in the first sentence

How do I select individuals who have, for example, both access and excel skills

The query as I have it will select all individuals who have either but I only want individuals who have both. I want to use a form and subform to do the filtering. Is that not apparent?

Include table and field names from all relevant data sources

I did in the query code.

 

[David R]

No you didn't. You posted the SQL of your query attempt, which tells us SOME things but not enough to be truly useful. Remember, we're volunteers here, help us help you by being specific and complete.

What does tableCandidates' structure look like? tableSkills? Also surely you also have a tableCandidateSkills...?

 

[plog]

I don't want clues, I want an explicit declaration. Also, my entire post made it quite clear not much was apparent. Lastly, if you produce sample data (in the manner I detailed) I can generate the SQL you want.

 

[scubadiver007]

The database I am using is for testing purposes only


"SkillList" table -

Field: "candidate ID"
Field: "skill"

Eg data

1 Excel
2 Excel
3 Access
3 Excel
4 Access

Using the above example, the query will return all of these but I would like a query that will return no.3 only.

The skills I want to select will be entered into

"searchlist" table

Field: "searchlist"

Is that helpful?

 

[plog]

This SQL will return results from your example:

SELECT [candidate ID]
FROM SkillList
WHERE skill='Excel' OR skill='Access'
GROUP BY [candidate ID]
HAVING COUNT([candidate ID])>1;

One note: this assumes that the data in SkillList is distinct--if someone is in there twice for the same skill they will be returned as meeting the criteria.

I didn't include this other table you mentioned, because you didn't provide an example of how it worked. If its germane to the situation, include a better data example.

 

[David R]

edit: hang on, that didn't work as expected.

 

[scubadiver007]

Each person does have multiple skills so they would be returned more than once.

I am using a subform to input the skills I need into an empty table and then link those records to the candidates (which is what I tried to explain in my first post).

I have updated your SQL code to reflect this:

SELECT SkillList.CandidateID, SkillList.Candidate
FROM searchlist INNER JOIN SkillList ON searchlist.searchlist = SkillList.Skill
GROUP BY SkillList.CandidateID, SkillList.Candidate
HAVING (((Count(SkillList.CandidateID))>1));

I hadn't considered using a GROUP BY query but I wonder whether a subquery might be more efficient (I have over 2000 records and almost 250 skills).

Thanks.

 

[David R]

You're very close, but change the last line to

HAVING (((Count(SkillList.CandidateID))=DCount("*","searchlist")));

That way it will work if you're searching 2, 3, or 17 skills.

 

[scubadiver007]

I am looking for individual people so whether they appear in the list 2, 3 or 17 times doesn't matter.

 

[David R]

Yes, but Count(SkillList.CandidateID))>1 will return people if they have 2 of the 17 skills you want. My version looks for how many skills you are asking for today, and returns only those people who have all of them.

 

[scubadiver007]

You're very close, but change the last line to

HAVING (((Count(SkillList.CandidateID))=DCount("*","searchlist")));

That way it will work if you're searching 2, 3, or 17 skills.

I realised soon after what you meant but I never got around to saying thanks.

 

[David R]

No problem, glad you got it working.