> 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.
I'm simply looking for ideas at this point but I'm not sure how having multiple tables would be jt-the way to go. > 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 jt-I only know enough to know that these are SQL commands. How would I implement this in the query editor. or is this possible? > 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 jt-I've got this ":associated_diseases" in a query. When I run the query nothing I enter in the parameter query box returns anything. > 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. jt-This didn't work for me either > 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. jt-So it isn't possible to just run a search like the one inside the form which finds any instance of the term you enter? > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > -- Patience yields far greater results than brute force or rage ever could so relax......it's just life !!! --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
