Using a comma separated list in a field as a filterin a query

[Guest]

I have a table that has a text field that contains a list of names separated
by commas.

Table 1
Cause Effect ID
Primary Tag Name
Associated Tags (this is a comma separated list)

Table 2
Tag Name
Tag Description
Other Tag information...

I want to create a query that will list all the records for a specific Cause
Effect ID and the details of all the tags in the Associated Tags field

This looked quite simple to me. I thought I could join the two tables where
[Tag Name] IN([Associated Tags]). I have not been able to get this to work.
I have tried adding quotes around each tag name in the Associated Tags comma
separated list but this didn't work either. What am I doing wrong?

 

[Guest]

I would advocate changing your table layout to one that is normalised.
Instead of having multiple tags in a single field, each tag should exist in
its own row.

Hope This Helps

 

[Marshall Barton]

I have a table that has a text field that contains a list of names separated
by commas.

Table 1
Cause Effect ID
Primary Tag Name
Associated Tags (this is a comma separated list)

Table 2
Tag Name
Tag Description
Other Tag information...

I want to create a query that will list all the records for a specific Cause
Effect ID and the details of all the tags in the Associated Tags field

This looked quite simple to me. I thought I could join the two tables where
[Tag Name] IN([Associated Tags]). I have not been able to get this to work.
I have tried adding quotes around each tag name in the Associated Tags comma
separated list but this didn't work either. What am I doing wrong?

You can not include syntactic elements in something that is
used as a value, so you original idea just won't fly.

However, you can use a criteria that does a similar kind of
thing:

SELECT table1.[Cause Effect ID],
table1.[Primary Tag Name],
table1.[Associated Tags],
table2.[Tag Name],
table2.[Tag Description],
table2.[Other Tag xxx],
FROM table1 table2
WHERE InStr(table1, table2) > 0

That Where clause may be a little simplistic, but additional
details are required before it can be refined.

 

[Van T. Dinh]

What you tried is actually syntactically incorrect.

For example, let's say we have:

[Associated Tags] = "TagA, TabB"

and if you want to select the details in [Table 2], with these 2 tags, you
need:

[Tag Name] In ("TagA", "TagB")

(note that there are 2 choices in the parentheses)

The way you had it:

[Tag Name] IN([Associated Tags])

will be deduced as:

[Tag Name] In ("TagA, TagB")

This will be interpreted as only ONE choice with value "TagA, TagB" and NOT
2 choices. Hence, your criteria won't work correctly.

Gerald's method is the correct way to structure this as recommended by the
Relational Database Design Theory.

 

[Guest]

Thanks for the InStr idea, Marshall. That worked great! I appreciate the
other replies as well and I understand the comments about a separate table.
Sometimes you have to work with what you are given and this is one of those
cases.

I have a table that has a text field that contains a list of names separated
by commas.

Table 1
Cause Effect ID
Primary Tag Name
Associated Tags (this is a comma separated list)

Table 2
Tag Name
Tag Description
Other Tag information...

I want to create a query that will list all the records for a specific Cause
Effect ID and the details of all the tags in the Associated Tags field

This looked quite simple to me. I thought I could join the two tables where
[Tag Name] IN([Associated Tags]). I have not been able to get this to work.
I have tried adding quotes around each tag name in the Associated Tags comma
separated list but this didn't work either. What am I doing wrong?

You can not include syntactic elements in something that is
used as a value, so you original idea just won't fly.

However, you can use a criteria that does a similar kind of
thing:

SELECT table1.[Cause Effect ID],
table1.[Primary Tag Name],
table1.[Associated Tags],
table2.[Tag Name],
table2.[Tag Description],
table2.[Other Tag xxx],
FROM table1 table2
WHERE InStr(table1, table2) > 0

That Where clause may be a little simplistic, but additional
details are required before it can be refined.