Access Query help

[rkook]

I have a database set up for contacts.

One of the fields in the table for employees is "skill set"
I.E. "project management" or "QA, Technical, and Operations"

If I make a querry for only "project management" I have no problem getting those individuals whose only skill set is "project managment" to show up

On the other hand, when I need to querry merely "QA" - the individuals who show up are those with only "QA" in their skill set.

I need to able to retrieve the "QA" out of the individual whose skill set is "QA, Technical, and Operations"

Do I need to create separate fields for each skill set? Or is it possible to retreive certain data out of the "skill set" field by using a criteria which only searches exactly what is under the skill set field?

Thanks, Rich

[NeoPa]

Do you have multiple Skill Sets per person / record?
Or do you need to select records which match any one of Skill Sets A; B; ...?

[rkook]

I'm pretty sure the answer is yes to both questions...

some people have multiple "skill sets" - which is the column.
Like Harry has A, B, C, like you said, and that is all in one record.

And yes, I need to be able to pull up all people in a querry with skill set A,
and i'll be building querries as I go - for example, all with skill sets A, C, and Z,

All the info is in one table at the present

[NeoPa]

In that case, we need to know how the multiple Skill Sets are stored in a single record.
Is it a text field with a particular character denoting the end of one and the start of another? or how exactly?

Multiple matching is ok when it's one-to-many, but many-to-many gets very fiddly.

Try to explain clearly what you've got; what you want; and use data that's not too complicated in itself, to avoid misunderstanding.

There are answers to most things somewhere - understanding the question is critical to finding the correct one.

[rkook]

i think how i'm storing the skill set(s) in the record is the problem.

They are text, but i DO NOT know how to write it in the record in any certain code or form.

The way they are listed in query now is as follows "AQ, manager, technical"
with no certain spacing or characters - that is what i'm looking for

[NeoPa]

Ah, now I better understand your original post.

Give me a while and I'll look into this.
We ARE getting into complicated territory here with many-to-many selection so some of the sneaky tricks won't work - prepare for longhand code.

I'll get back to you.

[NeoPa]

If you formulate your skills as ALWAYS having separator characters surrounding each item (I'll use the vertical bar as this is my habit) then that will make life easier later.
EG. If you have skills A; B; C; D; E; F; G then User 1 might have skill C (or "|C|") and user 2 might have skills B, D & E (or "|B|D|E|"). Notice the bar before and after each item - including the first or only item.

We need to use the function InStr() - Help system gives full details of function.
If you want a list of users who have skill D, for instance, you :-

SELECT *
FROM tblUsers
WHERE InStr(Start:=1, String1:=[Skill Set], String2:="|" & "D" & "|")>0

NB. Although it's coded here showing the parameter names, this function only works correctly if parameters are passed positionally. So, to work, it should be :-

SELECT *
FROM tblUsers
WHERE InStr(1, [Skill Set], "|" & "D" & "|")>0

To select more than one skill set (User must have skill A AND D or even A OR D) then you must put the ANDs or ORs in the WHERE clause between multiple 'InStr's

Have fun

[NeoPa]

Just noticed something that might confuse.
In previous post where the code says

..., "|" & "D" & "|")...

, that's where you can replace the hard-coded "D" with a variable or some other. If you were just checking for D you could use "|D|" instead of course.

[MMcCarthy]

SELECT * FROM TableName
WHERE [skill set] Like '*QA*';

I have a database set up for contacts.

One of the fields in the table for employees is "skill set"
I.E. "project management" or "QA, Technical, and Operations"

If I make a querry for only "project management" I have no problem getting those individuals whose only skill set is "project managment" to show up

On the other hand, when I need to querry merely "QA" - the individuals who show up are those with only "QA" in their skill set.

I need to able to retrieve the "QA" out of the individual whose skill set is "QA, Technical, and Operations"

Do I need to create separate fields for each skill set? Or is it possible to retreive certain data out of the "skill set" field by using a criteria which only searches exactly what is under the skill set field?

Thanks, Rich

[NeoPa]

I overlooked the 'LIKE' construct in my answer - that's a much better way of doing it.
I would recommend using the separator characters in a search though, as that precludes overlap.
To illustrate :-
You may have an 'IT' skill set as well as an 'IT Developer' skill set.
To search for just 'IT' you need to have separator characters around it...

WHERE [Skill Set] LIKE "*|IT|*"

...finds ONLY the 'IT' one.