Need help with a WHERE clause

[DB88]

Hello,

One of my fields is a text string containing a concatenated list of numbers (i.e. "3;15;1"). I would like to write a WHERE clause such that records whose concatenated list contain certain numbers are included in the SELECT. For example, lets say that I want to find all records that have a 3 and 15 from the table below.



ConcatList
---------
3;15;1
2
3
15
3;6;15

The WHERE clause should return the records for "3;15;1" and "3;6;15" but I'm not sure how to write the statement. I can't use WHERE ConcatList Like "3;15*" as it would exclude the record for "3;6;15". I also tried WHERE (ConcatList Like "3*" and ConcatList Like "15*") thinking that I was saying select records where the string has a 3 and 15 but code returned no records.

Any ideas?

[JoeM]

"15*" will only return records that start with a 15. If the number can be anywhere in the string, you want asterisks before and after the number, i.e.

WHERE ConcatList Like "*3*" And ConcatList Like "*15*";

[DB88]

Great, thanks!

But I discovered a new problem. If I look for "3" anywhere in the string then I will pull records for 30, 13, 33, etc. I don't want to write a supper complicated WHERE clause but I think the only way to solve is :
WHERE (ConcatList Like "3*" or ConcatList Like "*;3" or ConcatList Like "*;3;*") {Find records where string starts with 3, ends with ;3 or contains ;3; }

Anyone got a simpler solution?

[JoeM]

On the contrary, I think any solution would actually be more complex, not simpler.
Like maybe using VBA to parse the list into an array and check each value.

The thing that is making this more difficult than it has to be is the fact that you are working with Non-Normalized data. Storing multiple values in a single field like that is not good database/table design and can make what should be simple tasks much more difficult to do.

[JoeM]

Note, if you are stuck working with that data, I just thought of a way that does not involve VBA.

Create a calculated field in your query that places a ";" at the beginning and end of your ConcatList values (so that every value is enclosed by semi-colons).
That calculated field will look something like this:

ConcatList2: ";" & [ConcatList] & ";"

Then place the Criteria under this calculated field like this:

Like "*;3;*" And Like "*;15;*"

That should return the records you want.

[DB88]

My data isn't actually stored as a concatentated list. That would be a terrible database design. I create the list and search as a quick and dirty work around.

I have a 1 v Many relationship in my database between Products and Features (one product record has many features). I'm building a search form where my users can find a product that has all of their specified features. Basically an "AND" search of related records. Previous forum guidance on this subject has lead me to believe there is no real easy or clean way to accomplish this. Plus the search form will include option for searching by several other fields as well making the code that much more elaborate.

[JoeM]

I create the list and search as a quick and dirty work around

If you create the list having the semi-colons at the beginning and end like I mentioned in my last post, you could use the methodology I proposed with having to create an extra calculated field.

Otherwise, if you let us know the design of the original table, we may be able to suggest other options.
Of course, if the Concatenated List represents different fields within a single record, than you probably still have de-normalized data and design issues (and maybe that is why you are doing it the way you are doing it).

[DB88]

The table, Feature has two fields: ID and FeatureName
The table, Product has two field: ID and ProductName
The table, LI_ProdFea has three fields: ID, kf_Feature and kf_Product
Product is connect to LI_ProdFea by ID=kf_Product. LI_ProdFea is connected to Feature by kf_Feature=ID

On a search form I have an unbounded multi-select list box showing all FeaturesNames and a toggle where user says if they want an "AND" or "OR" search. The "OR" search was easy. Now, I'm working on the "AND". Basically, I want to return a (unique) list of records from Product table that have all of the user specified features.

[JoeM]

I don't quite see how you get from your last post to what you were doing.
It may be helpful to provide a copy of your database (with any sensitive data removed) for analysis. I cannot download it from my current location, but can tonight (and others may jump in too).

[DB88]

I have query with the Product table where I create a expression that produces a concatenated list of IDs. Each ID in the list corresponds to a related Feature record. User selects the features they want to search for in my generic form and a quick VBA code generates the SQL statement we have been talking about in this forum thread. Thus a quick and dirty "AND" search on related records. I've already built it in my database and it works great

Thanks for the offer, but I don't have time to prep database for outside analysis. Have to deliver "working" version to users by tomorrow afternoon. Quick and dirty is going to have to suffice

[JoeM]

OK. The method I proposed is post #5 should work with your current situation.
(Maybe not optimal, but should satisfy "quick and dirty").