Searching For A Value In A Column Row Using The "In" Clause

[lhooker]

Is there anyway to search each row within one column for a value ? The "IN" clause appears not to work for multiple values in a row.


SELECT Table_Payee_List.CategoryIDs, Table_Payee_List.PayeeSelectBox INTO GOTIT
FROM Table_Payee_List
WHERE (((Table_Payee_List.CategoryIDs) In ("25")));


Data (two columns with 5 rows) are represented below:

Payee CategoryIDs
Column Column

John Doe 21, 27, 32, 34, 44 - Row 1
Jane Doe 20, 25, 28, 42, 44 - Row 3
Paul Doe 19, 25, 28, 42, 44 - Row 3
James Doe 56, 29, 31, 62, 54 - Row 4
Amy Doe 24, 25, 29, 42, 74 - Row 5

 

[jdraw]

Can you step back and give readers an overview of the database involved in plain English.?

Is there anyway to search each row within one column for a value ?

Isn't that the role of a SELECT query.

for multiple values in a row.

Are you using multivalue fields??

I think a plain English description of the database and the issue would be most helpful.

 

[Galaxiom]

The IN function is the opposite of what you are expecting. It tests the whole field against each of the value in the function argument.

The problem is your data structure. It should be held as a Many-to-many relationship requiring a junction table with one record for each Payee-Category combination.

If you cannot control this then use
WHERE Table_Payee_List.CategoryIDs Like "*25*"

However this is also going to find anything 25 such as 125 or 251.

 

[lhooker]

Galaxiom,

You're correct . . . a "Like" statement would result in "25", "125", "225" . . . etc. (when searching for "25"). Got any ideas how to get around this ?

 

[Galaxiom]

Got any ideas how to get around this ?

Structure the data properly with a junction table. Otherwise you will continually be trying to work around the problems caused by inappropriate data structure.

 

[lhooker]

jdraw,

In essence, the query needs to retrieve all "Payees" that have "25" (or any other specified number) in a group of numbers of a row for a certain column (i.e. a cell, if this was an EXCEL spreadsheet). Thanks ! ! !:banghead:

 

[Galaxiom]

The kludge is to use this:

WHERE Table_Payee_List.CategoryIDs = "25"                ' Only entry
OR Table_Payee_List.CategoryIDs Like "*, 25 ,*"          ' In the middle
OR Table_Payee_List.CategoryIDs Like "25, *"             ' At the beginning
OR Table_Payee_List.CategoryIDs Like ", 25"              ' At the end

Another alternative is to match a pattern with a Regular Expression.

 

[lhooker]

Galaxiom/JDraw,

Thanks for your input ! ! ! I just completed the creation of a junction table. It works and I learned something new. The creation and use of this junction table was pretty slick. Now, I have to figure out how to apply this technique in a VBA SQL statement (i.e. passing a value from a form to this VBA SQL statement) using this junction table.

Thanks again ! ! !