String Search Question

[jpvonhemel]

Hello,

I have a table with five string fields that contain patient reasons for visit and diagnosis. I need to search these five string fields for a subset of words that indicate a record has a visit due to injury. I had been using a sql query with. WHERE [fldReasonForVisit] Like “*Fall*” or Like “*Fracture*” Or Like “*GSW*” and so on evaluating just one field.

My new table has now has five fields that would need to be evaluated and my list of search words is identical for each of the five fields. It doesn't feel right repeating the same where statement for each of the five fields in a sql query.

I was thinking I might be able to create an array for each keyword and pass the fields as arguments into a function and loop through the array and return a value of true or false for each record. Is this feasible or is there a better way. Its been years since I had to create vba code and I cannot seem to find any examples to guide me on the web.



thanks
jerold

[Micron]

On the surface, your problem seems to be that your table was designed with a spreadsheet brain, and the developer of this db is suffering from Excelitis.
You don't put related data across in fields (columns) you put it in rows. That is the premise of relational database design. If each of your "things" could fit into one box, I suspect it would be VisitReasons (for example). Thus each reason goes into a record, not a field. If the reason's 'parent' is a visit, then you need a table for visits, which is related to the reasons for that visit. I think you might do well to study database normalization to fully grasp that example of a one to many relationship.

To your question about getting what you want out of your db via code, virtually anything is possible if you don't mind struggling. It is also possible to fill a swimming pool with a spoon, but no doubt you get the absurdity of that. To carry on with a bad db design means you're going to struggle now and in the future and it is not really any smarter than the spoon thing.

Sorry if I've misinterpreted your level of normalization knowledge.

[CarlettoFed]

Perhaps by attaching a sample file then you can get yourself to see and understand the design mistakes you made.

[jpvonhemel]

Down “deep“, this is starts with a table that is imported from SQL server and out if my control or expertise. This table Is sourced from data that is pulled from patient records that has some great standardized fields, but also suffers from free text crap, also out of my control.

Unfortunately my job starts with differentiating patients using the free text nonstandard data.
It has been done for years by printing reports and going record by record with a ruler and a pencil, which is very demoralizing when one knows it does not have to be this way. My please for a standard field to pull from have not been embraced.

My goal is to take a task that can take several hours and distill it down to 15-20 min, with the understanding that it is not normalized and not a 100% sound method.

What I can do is combine the five columns of data I need to search into a single calculated field. That would simplify it down to a single place, but it is still nonstandard string data subject to misspellings, etc.

On the surface, your problem seems to be that your table was designed with a spreadsheet brain, and the developer of this db is suffering from Excelitis.
You don't put related data across in fields (columns) you put it in rows. That is the premise of relational database design. If each of your "things" could fit into one box, I suspect it would be VisitReasons (for example). Thus each reason goes into a record, not a field. If the reason's 'parent' is a visit, then you need a table for visits, which is related to the reasons for that visit. I think you might do well to study database normalization to fully grasp that example of a one to many relationship.

To your question about getting what you want out of your db via code, virtually anything is possible if you don't mind struggling. It is also possible to fill a swimming pool with a spoon, but no doubt you get the absurdity of that. To carry on with a bad db design means you're going to struggle now and in the future and it is not really any smarter than the spoon thing.

Sorry if I've misinterpreted your level of normalization knowledge.

[jpvonhemel]

As stated in my last post, I did not design the table, I have imported it to access. The table corresponds 1:1 to data from patient records. It reads like I first need to combine each of the five fields to a single field and work if of that. I see that while not making it clean, removes complexity in queuing for my injury strings.

[orange]

You are asking for assistance, but I am not clear what exactly you are seeking, nor what your longer term goal may be. You recognize that non standard(free text) values lead to uncertainty/inconsistency and render common database approaches(searching/comparing/reporting) almost useless. You seem to be in the middle of a project/issue that needs a response in the short term. But what about a longer term solution--sticking with the structure you have does not seem to improve things. How important is that data/application to your organization? Who, beside you, can operate/resolve requests of the existing data?

My recommendation is that you supply a database showing your table structures and some sample records. But you should change the names of the "patients" to Porky Pig, Polly Dactyl, John Doe etc to protect any confidentiality. Readers need to see what you actually have in order to offer focused help/advice.

Good luck.

[CarlettoFed]

Do you enter the values to be searched for using text controls or other?

[jpvonhemel]

You are asking for assistance, but I am not clear what exactly you are seeking, nor what your longer term goal may be. You recognize that non standard(free text) values lead to uncertainty/inconsistency and render common database approaches(searching/comparing/reporting) almost useless. You seem to be in the middle of a project/issue that needs a response in the short term. But what about a longer term solution--sticking with the structure you have does not seem to improve things. How important is that data/application to your organization? Who, beside you, can operate/resolve requests of the existing data?

My recommendation is that you supply a database showing your table structures and some sample records. But you should change the names of the "patients" to Porky Pig, Polly Dactyl, John Doe etc to protect any confidentiality. Readers need to see what you actually have in order to offer focused help/advice.

Good luck.

I have attached a compressed sample database with what I am dealing with, including a query with how I search. After reading the responses I did combine the individual fields to a single calculated field in the table.

I hope this gives some idea of what I am dealing with. I understand what I am dealing with is not at all ideal, but anything I can improve upon beyond hand scanning paper with a ruler is an improvement for me I will take. Going out beyond a 1-1.5 years is in the works. This is a short term fix and I am hopeful the process can be improved with improvements that are planned and just now being worked on.

If there is a better way to query for a list of strings that represent injury, I would love to learn and make this cleaner. I cannot however expect the process that the data originates from to change, atleast in the short term. It would be ideal if the diagnoses were coded at the arrival stage, but unfortunately they are not. That may change at some point and then I could change up to evaluating for specific ICD-10 codes.

[jpvonhemel]

The come mostly from the emergency department report or admissions records. The table data is what I get from the organization.

[Micron]

What I can do is combine the five columns of data

I will also take a look at the db a bit later keeping in mind my current thought about combining your fields into one field by using a Union query - as long as that query doesn't need to be updatable. However, the Union can be used to 'normalize' those fields by writing them to a staging table. Not sure if that data would need to be temporary, or if it can remain there, but it should be editable at that point.

EDIT - where/how you'll get/pass the criteria values will be important. Since I'm reminded that the IN operator cannot use wildcards, you may have to concatenate several LIKE operators. So if you want to specify "bite" and "fall" in one query execution and those words can be anywhere in the field, I'm seeing the need for a function that will parse the list of words and build the LIKE string and run a query (would need to modify the query def on the fly) or build the sql in code. So where those keyword values would come from is important. Perhaps a multi-select listbox? That way, if your keyword values are edited or added, the changes would be easy to handle. Still thinking that all of this would still be based on a UNION query or a set of table records that it populates. You could also take the code approach and UNION the records and just show them in a form or report I guess (dispensing with the staging table).

[orange]

What exactly does your sample query indicate? That is, what does he result mean?

SELECT tblStrings.[fldAccountNumber], tblStrings.[fldMRN], tblStrings.[fldPatientName], tblStrings.[fldArrivalDate], tblStrings.[fldStatedComplaint], tblStrings.[fldReasonForVisit], tblStrings.[fldPrimaryDx], tblStrings.[fldSecondaryDx], tblStrings.[fldTertiaryDx], tblStrings.[calcAllRFVFields]
FROM tblStrings
WHERE (((tblStrings.[calcAllRFVFields]) Like "*Thorax*" Or (tblStrings.[calcAllRFVFields]) Like "*Bite*" Or (tblStrings.[calcAllRFVFields]) Like "*MVC" Or (tblStrings.[calcAllRFVFields]) Like "MCC" Or (tblStrings.[calcAllRFVFields]) Like "*Assault*"));

MCC?
MVC ---motor vehicle collision?

I was hoping for some categorization of Injuries and some keywords/symptoms related to each.
There are some lists shown here that could be used to simplify overall processing. But it depends on what is really needed and what resources you (org needing the info) have for this project.

For example: If you had a table of injury types and for each injury type there was a predefined(at least partially) list of symptoms/description, then you could search for a number of these symptoms in the incoming data and assign a most likely injury. It would not be 100 % but would improve over time as words are added/removed/adjusted. You could even assign some weights to a particular patient visit based on the number of keywords found per injury type. In addition you could identify the part of the body affected and perhaps more words to focus the injury. Generally more categories and identifier/keywords within the category to refine the weight/diagnosis. Just some wild suggestions to help focus the issue your query and process is trying to solve and the eventual usage of the result.

Good luck with your project.

[CarlettoFed]

In the attachment you can see an example of how you could do it

[jpvonhemel]

I will also take a look at the db a bit later keeping in mind my current thought about combining your fields into one field by using a Union query - as long as that query doesn't need to be updatable. However, the Union can be used to 'normalize' those fields by writing them to a staging table. Not sure if that data would need to be temporary, or if it can remain there, but it should be editable at that point.

EDIT - where/how you'll get/pass the criteria values will be important. Since I'm reminded that the IN operator cannot use wildcards, you may have to concatenate several LIKE operators. So if you want to specify "bite" and "fall" in one query execution and those words can be anywhere in the field, I'm seeing the need for a function that will parse the list of words and build the LIKE string and run a query (would need to modify the query def on the fly) or build the sql in code. So where those keyword values would come from is important. Perhaps a multi-select listbox? That way, if your keyword values are edited or added, the changes would be easy to handle. Still thinking that all of this would still be based on a UNION query or a set of table records that it populates. You could also take the code approach and UNION the records and just show them in a form or report I guess (dispensing with the staging table).

If I could create a function that references keyword values from a separate table, that would make it easier to add or take away keywords. One keyword I was using is“*cut*” but it would pull every record with the string “Acute”, which is in many if not most patients who are admitted, thus defeating the benefit.

The keyword values are my concoction based on what I deal with day to day. It is not at all a complete or definitive list. There is trial and error involved when patients are missed and when there are false positives.

If I am able to successfully identify a record with an injury keyword and it is real, I would flag them with a 1 in a separate field. If it was not an injury, it gets a zero. For those patients that are flagged with a 1, I pull them into a report and email it out via macro.

[jpvonhemel]

Thank you very much CarlettoFed.
I will take a look at this hopefully tomorrow. I really appreciate you taking the time to help me!

[jpvonhemel]

Do you enter the values to be searched for using text controls or other?

Sorry, the values for Reason For Visit and Stated Complaint are free text entered into the hospital system when a patient presents to triage/admissions. The three diagnosis fields corresponds to three lines in the Physician Emergency Report.