Searching a string for specific entries

[eussias]

I need to write a query which will select specific patients with a particular Medical History. The history for each patient has been entered in a field (Med_Hist) with numerous options. The history has been categorised.. eg.. Cardiovascular is entered as CAO and Epilepsy is entered as EP.
The problem is that each patient can have more than one of these in their history, so the Med_Hist field (String type) could be any length. Each relevant entry is separated by a space, so someone who has both Cardiovascular and Epilepsy would have a Med_Hist of CAO EP.
I need to determine the number of patients who have each specific Med_Hist "side effect" and need to know how to write a query that searches through the entire Med_Hist string looking for specific values.
Hope this is clear and that someone can be of assistance. Cheers

 

[930driver]

You can use the Instr() function to find text.

However, you have a more fundamental problem. You are running into troubles because your database is not normalized. You should never store multiple values in one field. These should be put in a separate table so that you'll have one record for each diagnosis. That way the querying becomes very simple.

If you create two tables;

The first called tblDiagnoses with fields as follows:
DiagnosisID, Autonumber
Diagnosis, Text, 255

And a link table, lktblPatientDiagnoses with fields as follows:
PatientDiagnosisID, Autonumber
PatientID, Long (or whatever corresponds to your unique patient ID)
DiagnosisID, Long

Then put a subform on your patient form. It's recordsource will be lktblPatientDiagnoses. This subform gets one combo who's record source is based on tblDiagnoses. Link the parent and subform on PatientID or whatever the key field is. Now you can assign one or a hundred diagnoses to each patient.

An added bonus; what if the transcriptionist misspells one of the diagnoses with your system? You'll never know. And the patient won't be properly reported. If you have a table of diagnoses you can check them and then no more data entry errors. "The Key, The Whole Key, and Nothing But The Key, So Help Me Codd!"

 

[GingerR]

hey there--

to search for one at a time, make a query and in the criteria of the Medical History field put

 like "*" & [Enter Code:] & "*"

(* is a wildcard)
run the query. when asked, put in one of your codes.

problem is that if you want to search for more than one at a time.....

something you might want to think about in the future is 'normalizing' your data. it will make it much better for things like you are having trouble with right now:

Create two tables:

tblDiagnosisCodes
DiagnosisID Autonum
DiagnosisCode Text CAO, EP, etc
DianosisDesc Text Cardiovascular etc

tblPatientDiagnosis (relates Patients and diagnoses, a many-to-many relationship)

PatientID Number brought in from Patient table
DiagnosisID Number brought in from Diagnosis table

setting up - you'd have to get all existing data into the right places. Are you using forms? if not, you'd make one for data entry with a subform based on tblPatientDiagnosis with a combo box based on tblDiagnosisCodes so data entry folks would pick all relevent diagnoses that relate to a patient. this is much easier to sort/filter on. Also ensure users are entering codes correctly when they can only pick from a list.

then, for reporting, i'd make another form (not just run queries) where the user picks from a combo box based on tblDiagnosisCodes which code they are looking for, click a button, and results come up.

you can expand so user can choose more than one diagnosis and find patients with specific multiple diagnoses too.

anyhow, you got the answer to your immediate need. hope the rest inspires some creativity!!

g

 

[GingerR]

oh--by the way-
based on what you need you could also put in query criteria things like

Like *CAO*
Like *CAO* or Like *EP*

(patients with Cardio OR Epilipsy)

Like *CAO* and Like *EP*

(patients with Cardio AND Epilipsy)