Need query to find for matches of one field within another field

[kelly.salvatori]

I'm looking for a way to find the field from one table within the
field of another table. For example:
Table1 => Field1 = Group1
Table2 => Field1 = Group1, Group2, ... , Groupn

The query would allow me to compare Table1.Field1 with Table2.Field1
and see that "Group1" is within it. I'm used to doing this with the
LIKE command using specific text and a wildcard (i.e. '%group1%) but
not using fields. Any suggestions on how I can accomplish this?

 

[John W. Vinson]

I'm looking for a way to find the field from one table within the
field of another table. For example:
Table1 => Field1 = Group1
Table2 => Field1 = Group1, Group2, ... , Groupn

The query would allow me to compare Table1.Field1 with Table2.Field1
and see that "Group1" is within it. I'm used to doing this with the
LIKE command using specific text and a wildcard (i.e. '%group1%) but
not using fields. Any suggestions on how I can accomplish this?

The Access (JET) wildcard is * rather than % - do you get the data with a
query like

SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.Field1 LIKE "*" & Table1.Field1 & "*"

or, if the Group1 field might be free text so that you could get ambiguities
such as

Table1 => Field1 = "AT"
Table2 => Field1 = "CATS, DOGS"

then include the delimiters:

SELECT Table1.*, Table2.*
FROM Table1, Table2
WHERE Table2.Field1 LIKE "*" & Table1.Field1 & ",*"
OR Table2.Field1 LIKE "*, " & Table1.Field1 & "*"
OR Table2.Field1 = Table1.Field1


Of course you're paying the penalty here for violating the principle that
fields should be atomic! Table2's design is simply WRONG.