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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 03 Nov 2005 21:00 GMT

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?

Reply to this Message

Gerald Stanley - 03 Nov 2005 22:10 GMT

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

Signature

Gerald Stanley MCSD

> I have a table that has a text field that contains a list of names separated
> by commas.
[quoted text clipped - 17 lines]
> separated list but this didn't work either. What am I doing wrong?
>

Reply to this Message

Marshall Barton - 04 Nov 2005 00:52 GMT

>I have a table that has a text field that contains a list of names separated
>by commas.
[quoted text clipped - 16 lines]
>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.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Joe - 07 Nov 2005 21:39 GMT

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.
[quoted text clipped - 34 lines]
> That Where clause may be a little simplistic, but additional
> details are required before it can be refined.

Reply to this Message

Van T. Dinh - 04 Nov 2005 01:00 GMT

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.

Signature

HTH
Van T. Dinh
MVP (Access)

>I have a table that has a text field that contains a list of names
>separated
[quoted text clipped - 21 lines]
> comma
> separated list but this didn't work either. What am I doing wrong?

Reply to this Message