Hi John, Some folks may tell you that the best way to handle this is with more then one table. But leaving that alone and addressing simply your question for the moment - using only one table.
Normally you could use either the POSITION or LOCATE or LIKE functions in your queries. So with a table Drugs and a field Treats as you describe one can use a query such as SELECT * FROM "Drugs" WHERE POSITION( 'depression' IN "Treats" ) > 0 to find all records with the word 'depression' in the Treats field. Small problem however, Base does not allow replaceable parameters inside function calls, so SELECT * FROM "Drugs" WHERE POSITION( :Presc_For IN "Treats" ) > 0 will never return a value. So for your single table and your 'set' type field you need to use wild cards with the LIKE command. The query would be SELECT * FROM "Drugs" WHERE "Treats" LIKE :Presc_For You will be prompted for Presc_For and you will need to enter the search string %depression% the '%' being the wild card character. There are a few things to be careful of going this way - the single field in a single table. Data consistency being the major concern. Assume that one time you enter the value "depression" in the field then in the next record "depression, hypertension" and finally in a third you enter "depression, hypertension" The first time you run the above query you search for %depression% - the results would be 3 records Second time you enter %hypertension% - results are 2 records The above is correct, yes. Now you run the query a third time and enter %depression, hypertension% - it will return only 1 record. It missed a record. I will leave it to you to see why that is...Hint - you must be very careful when you enter your data. --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
