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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
Joe said:
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?
Click to expand...


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.
 
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.
 
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.

Marshall Barton said:
Joe said:
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?
Click to expand...


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.
Click to expand...
 
Back
Top