Filtering when multiple attributes in one cell?

[amzg]

One of the fields in my database contains varied attributes, and an irregular amount of such. To exemplify; for a 'Flowers data base" the flower 'Rose' can under field 'Characteristics' have e.g; "tall, thorny, sweet smelling, thin leaves" whereas the flower 'Dandelion' under 'Characteristics' may read; "short, sweet smelling" - i.e a different number of characteristics and partially or no overlap in actual attributes so it is not (?) the best idea to split up the contents into separate fields as far as I can tell.

And... you guessed it... now I wish to sort/filter/create queries.

I'm just a beginner so I humbly ask you more experienced guys in here; what would you do? Is there perhaps a way to filter and group based on "substings (strings in stings)"? What else?

Thank you!!

[Sliderule]

amzg:

I hope the following explanation will be helpful.

Since, you have designed your database with the "Characteristics" field to have a possibility of multiple 'characterictics', you will have to SEARCH for a 'text string' anywhere in the field. I mean, it might be at the beginning, the middle, or, the end.

In the database search language:

1. the word for a 'partial' search is LIKE
   
2. Percent is used to indicate a 'wild card' search.
   
   'MySearch%' means it must BEGIN with 'MySearch'.
   
   '%MySearch%' means it can be anywhere in the field.
   
   '%MySearch' means it must END with 'MySearch'
   
   
3. Text literals are surrounded by single quotation marks: ' . . . such as: 'MySearch'
   
4. Table and Field (Column) names are enclosed in double quotes " , such as: "Characteristics"
   
5. Notation for a Parameter Prompt begins with a semi-colon : and is ONE WORD ( no spaces ), such as: :Enter_Search_String
   
6. Two pipe characters, withOUT a space between them || is used to CONCATENATE strings of text together, such as: '%' || 'sweet smelling' || '%'
   

OK, I created an example of your table as seen below:



To create your Query, please follow these steps:

1. Open your database in OpenOffice
   
2. Under Database - press Queries
   
3. Under Tasks - press Create Query in Design View...
   
4. An Add Table or Query pop-up will be displayed, select your table name, press Add button
   
5. Press Close button
   
6. On the lower half of the screen will be a line named Field . . . press the down button and select Flower ( or whatever is the Field in your table that is appropriate )
   
7. In the next column, select the column that contains the data you want to search, in this example, it will be Characteristics
   
8. In the line Criterion under Characteristic, enter

   Code:

   LIKE '%' || 'sweet smelling' || '%'

   
   
9. Press the Run Query Icon on the ToolBar, OR, F5
   
10. You may elect to SAVE this Query from the ToolBar, if you wish, and, assign the saved query a name

- - - - - - - - - - - - - - - - - - - - - - - - - - - -
Below, I will attempt to show you how to create a PARAMETER QUERY, where, the Query will PROMPT you for input ( in this case, the string of text you want to search for in the "Characteristic" field ( column ).

1. Open your database in OpenOffice
   
2. Under Database - press Queries
   
3. Under Tasks - press Create Query in Design View...
   
4. An Add Table or Query pop-up will be displayed, select your table name, press Add button
   
5. Press Close button
   
6. On the lower half of the screen will be a line named Field . . . press the down button and select Flower ( remember the name will be your table name -- )
   
7. In the next column, select the column that contains the data you want to search, in this example, it will be Characteristics
   
8. In the line Criterion under Characteristic, enter

   Code:

   LIKE '%' || :Enter_Search_String || '%'

   
   
9. Press the Run Query Icon under the menu line, OR, F5
   
10. A popup will appear prompting you to input a Value . . . in this case, put the text you want to search
    
11. Press the OK button
    
12. You may elect to SAVE this Query from the ToolBar, if you wish, and, assign the saved query a name.
    
    Next time you run this Saved Parameter Query with the Prompt, it will ask you to enter the search string

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post title ( edit button ) if your issue has been fixed / resolved.

[MSPhobe]

The alternative is to re-do your database.

I know this will not be very palatable advice... but what you have done is a Bad Thing that MANY people do..... and it paints you and them into corners that are a pain to deal with.

At first, doing things like your flowers characteristics the Right Way, seems odd and unsatisfactory, but once you get the hang of it, so many things work so much better.

Table 1: Flowers

ID: integer, primary key
Name: text

Table 2: Characteristics
ID: integer, primary key
Description: text

Table 3: WhoHasWhat
ID: integer, primary key
Flower: integer <- this filled with integers from Flowers.ID
Characteristic: integer <- this filled with integers from Characteristics.ID

Sample data....

Flowers...

1: Rose
2: Bramble

Characteristics

1: Thorns
2: Tasty fruit
3: Smelly flower

WhoHasWhat

[amzg]

My GOSH! You guys are the greatest! What generosity and kindness! My sincerest appreciation for your - to say the least - thorough answers!

And, actually, thanks to getting your advice I've realized I should actually use both(!) alternatives!
In case you're wondering why/how;

Because of the 'free' nature of the information, there will likely be synonyms included, such as "smelling" and "scent". Rather than keeping e.g "thorny" and "smelling" in the same cell and, say, "scent" in another, I will group synonyms into the same cells but otherwise split it up into different rows.

Again, my sincerest thanks MSPhobe and sliderule!

[amzg]

[jmunoz]

Hi,

Posting this, just in case is useful for others.

As far as MySQL don't accept the standard "||" concatenation operator you need to use CONCAT function, but looks like there is a bug with the OO Design view.

This is the SQL query what worked for me: