Display/Look-up a String of Values in a Query?

[JL]

Can anyone help me with how to write a query to display (look-up) a string
of values from another table?

How do I write a query to display the SkillName field along with the other
Member details?

I have a SkillSets table containing fields "SkillName" (text) and "SId"
(text 3 chars.) which is the primary key.
E.g. Table "SkillSets"
SId SkillName
T21 Telesales
M36 Management

I also have a table named Members with a field "Skills" that holds the
member's various skills (a comma delimited string of values corresponding to
the "SId" in the SkillSets table).
E.g. Table "Members"
Name Country Skills
Mark UK M14, T22, D23, B16
Anne USA A12, T22

I have tried various combinations of JOINs but when the field has more than
1 single SId value separated by a comma, nothing displays.

This sort of thing is what I've been trying to use - but to no avail:

SELECT [M.Name], [M.Country], [S.SId], [S.SkillName], [M.Skills]
FROM Members AS M LEFT JOIN SkillSets AS S ON S.SId LIKE M.Skills
GROUP BY M.Name, M.Country, S.SId, S.SkillName, M.Skills

(I've tried various versions of this type of query - the single values of
the M.Skills fields display the correct SkillName but multiple values do
not).

What I want to achieve is the following, via a query (or any other method):

Name Country SkillNames
Mark UK Management, Teaching, Driving, Banking
Anne USA Accountancy, Teaching
etc.

Any help is greatly appreciated.

JL

 

[Ken Snell]

Your table setup is not normalized, and this is creating the major headache
for you to do what you wish.

Before we try to come up with a way to do what you seek using your setup, is
it possible for you to change your tables to this structure:

tblMembers
MemberID (primary key)
MemberName <--- don't use Name as a field name
MemberCountry
(etc.)

tblSkills
MemberID (composite primary key with SD)
SID (composite primary key with MemberID)


This setup uses a junction table (tblSkills) to give you a Many-to-Many
relationship between members and skills. The structure of tblSkills now
makes writing a query much easier to do.

Post back with info re: whether this change in table structure can be made.
Then we'll go from there.

 

[John Nurick]

Hi JL,

The problem is that you're storing multiple values in a single field,
and relational databases aren't designed to handle that.

What you have is a many-to-many relationship between Members and
SkillsSets, and this needs to be implemented by a third table
MemberSkills (assume MemberID is the primary key of Members, and Mark
has MemberId=1); include both fields in the primary key:

MemberID SId
1 M14
1 T22
1 D23
1 B16
i.e. each value in your present Members.Skills field becomes a record in
MemberSkills.

If you set up this table, simple joins will deliver the output you want.

To populate the table, use a series of append queries, one for each
possible value of SId, something like this:

INSERT INTO MemberSkills (MemberID, SId)
SELECT Members.MemberID, "M14" As SId
FROM Members
WHERE Members.Skills LIKE "*M14*";

Can anyone help me with how to write a query to display (look-up) a string
of values from another table?

How do I write a query to display the SkillName field along with the other
Member details?

I have a SkillSets table containing fields "SkillName" (text) and "SId"
(text 3 chars.) which is the primary key.
E.g. Table "SkillSets"
SId SkillName
T21 Telesales
M36 Management

I also have a table named Members with a field "Skills" that holds the
member's various skills (a comma delimited string of values corresponding to
the "SId" in the SkillSets table).
E.g. Table "Members"
Name Country Skills
Mark UK M14, T22, D23, B16
Anne USA A12, T22

I have tried various combinations of JOINs but when the field has more than
1 single SId value separated by a comma, nothing displays.

This sort of thing is what I've been trying to use - but to no avail:

SELECT [M.Name], [M.Country], [S.SId], [S.SkillName], [M.Skills]
FROM Members AS M LEFT JOIN SkillSets AS S ON S.SId LIKE M.Skills
GROUP BY M.Name, M.Country, S.SId, S.SkillName, M.Skills

(I've tried various versions of this type of query - the single values of
the M.Skills fields display the correct SkillName but multiple values do
not).

What I want to achieve is the following, via a query (or any other method):

Name Country SkillNames
Mark UK Management, Teaching, Driving, Banking
Anne USA Accountancy, Teaching
etc.

Any help is greatly appreciated.

JL