Is that SQL SELECT possible 1

[pss08]

I have a table with, say, patients.

In that table I have multiple records for each patient with a memo field on it. Is it possible to filter patients that have records containing some text.

For example, I need to know how many patients that in the memo field of one of his records contains "headache", and in the memo field of another record of the same patient contains "aspirin" on it.

 

[mp9]

If it was a text field you could search using LIKE in a query, e.g. LIKE '*headache*'

However, I don't think a MEMO field supports this operation.

http://www22.brinkster.com/accessory/

 

[pss08]

I can search in the Memo field using LIKE, but what I need is to know what patients have records with some values. I can not do the following:

SELECT PacCode FROM PacHistory WHERE MemoHist LIKE '*headache*' AND MemoHist LIKE '*aspirin*'

or

SELECT PacCode FROM PacHistory WHERE MemoHist LIKE '*headache*' OR MemoHist LIKE '*aspirin*'

Because there is a record that contains '*headache*' and another different record that contains '*aspirin*' both pertaining to the same patient. I need to know the patients that have only both kind of records.

 

[PHV]

You may try this:
SELECT A.PacCode
FROM PacHistory A INNER JOIN PacHistory B ON A.PacCode = B.PacCode
WHERE A.MemoHist LIKE '*headache*' AND B.MemoHist LIKE '*aspirin*'


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886