Search code examples
ms-accessselectdelimited

Select values from table where value in comma delimited string

this is my first post... I'm busy writing up a piece of software in Access 2010. My SQL's not too bad, but I'm a little stumped here.

I have a table with the following layout

ID = AutoNumber, 
Category = Text, 
SubCategory = Text, 
Grades = Text.

And the following data

ID  Category    SubCategory   Grades
2   Behaviour   Good          RRR,RR,R,1,2,3,4,5,6,7,8,9,10,11,12
3   Behaviour   Not so Good   RRR,RR,R,1,2,3,4,5,6,7,8,9,10,11,12
4   Health      Doctor Note   RRR,RR,R,1,2,3,4,5,6,7,8,9,10,11,12
5   Social      Peer Pressure   
6   Academics   General Academic Knowledge      1,2,3
7   Academics   Additional Academic Knowledge   1,2,3
8   Gross Motor Skills  Kicks a ball that is moving towards him or her. 
9   Gross Motor Skills  Hops and jumps while in motion  R
10  Gross Motor Skills  Skips   RR
11  Gross Motor Skills  Gallops RR
12  Gross Motor Skills  Jumps forward 10 times  RR
13  Gross Motor Skills  Catches a small bounced ball    RR
14  Gross Motor Skills  Turns somersault    RR
15  Gross Motor Skills  Alternates feet walking up or down stairs   RR`

I'm trying to create a query where I select for eg

SELECT SubCategory from CATEGORY WHERE GRADES LIKE R

and it should return

Good
Not so Good
Doctors Note
Peer Pressure
Hops jumps and Skips

and obviously if I require Grades RRR or 1 or 2 or 5 it should select as needed

please can anyone assist. Thanks Warren

Solution

  • You can do it with the operator LIKE this way:

    SELECT SubCategory 
FROM CATEGORY 
WHERE ',' + GRADES + ',' LIKE '*,R,*'

    or if you want the queried value 'R' separately as a parameter:

    SELECT SubCategory 
FROM CATEGORY 
WHERE ',' + GRADES + ',' LIKE '*,' + 'R' + ',*'