By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,919 Members | 1,587 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,919 IT Pros & Developers. It's quick & easy.

Access Query help

P: 3
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
Oct 24 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,471
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; ...?
Oct 24 '06 #2

P: 3
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
Oct 24 '06 #3

NeoPa
Expert Mod 15k+
P: 31,471
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.
Oct 24 '06 #4

P: 3
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
Oct 24 '06 #5

NeoPa
Expert Mod 15k+
P: 31,471
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.
Oct 24 '06 #6

NeoPa
Expert Mod 15k+
P: 31,471
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 :-
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tblUsers
  3. 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 :-
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM tblUsers
  3. 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
Oct 24 '06 #7

NeoPa
Expert Mod 15k+
P: 31,471
Just noticed something that might confuse.
In previous post where the code says
Expand|Select|Wrap|Line Numbers
  1. ..., "|" & "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.
Oct 24 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Oct 25 '06 #9

NeoPa
Expert Mod 15k+
P: 31,471
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...
Expand|Select|Wrap|Line Numbers
  1. WHERE [Skill Set] LIKE "*|IT|*"
...finds ONLY the 'IT' one.
Oct 25 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.