Medical reporting: A Case Study

During the version 2.0 beta trials, we received a help request that among other things included the following:

First and foremost, I would like to generate a query like "What is the average difference in Sgawpp between visits 1 and 4 for patients having a diagnosis of 493.00 and taking Serevent compared to patients taking Flovent?" And graph the results.

Our immediate reaction was Dream On! But then some of the techies got to kicking this around.

Since the database contains confidential information, we can't provide this as a downloadable example. But we can outline what was done to satisfy this user.

Thank you for giving us this opportunity to help you out with your demo. After reviewing your database, we think you'll be able to do a smashing demo of how natural language can radically improve data access for this information.

As you suggested, the first step is to narrow down the list of tables to the ones you need. From the information you provided, we start with the following set:
ChiefComplaint, Diagnosis, MedSample, PatDiags, PatientID, Patients, PatMeds, PatPFTS.

The important thing is to make sure that you're working with a connected set of tables via the Relationship map.

You write:
<< First and foremost, I would like to generate a query like "What is the average difference in Sgawpp between visits 1 and 4 for patients having a diagnosis of 493.00 and taking Serevent compared to patients taking Flovent?" And graph the results.>>

To do this you'll also need to create a query that defines the general properties of this kind of comparison so it can be used in English queries. For instance, you could define a query called Effectiveness:
SELECT PatPFTs.PatientAccountID, PatPFTs_1.VisitNum AS PatFirstVisit, PatPFTs.VisitNum AS PatLaterVisit, PatPFTs.sGAWpp-PatPFTs_1.sGAWpp AS Change FROM PatPFTs INNER JOIN PatPFTs AS PatPFTs_1 ON PatPFTs.PatientAccountID = PatPFTs_1.PatientAccountID WHERE (((PatPFTs_1.VisitNum)=1) AND ((PatPFTs.VisitNum)>1));

This simply allows Microsoft Access to do what it does best, the mathematical parts of the operation, leaving ELF free to handle the linguistic aspects. This query defines "Change" as the difference in sGAWpp between the first visit and all subsequent visits.

Because of a bug in the current version, you may need to close the database and reopen it in order to get newly defined queries to appear in the list of selectable Data Sets. You'll also need to click the Analyze Queries check box.

After running the Analysis on this set of tables, the query
Compare the average change for Serevent and Ventolin by patientlastname, PatLaterVisit and brand_name
translates in a few seconds into:
Transform avg ( Effectiveness.CHANGE ) AS [avg of CHANGE] SELECT DISTINCTROW MedSample.brand_name , Effectiveness.PatLaterVisit FROM PatPFTs , PatientID , Effectiveness , PatMeds , MedSample , PatPFTs INNER JOIN PatientID ON PatPFTs.PatientAccountID = PatientID.PatientAccountID , PatPFTs INNER JOIN Effectiveness ON PatPFTs.PatientAccountID = Effectiveness.PatientAccountID , PatientID INNER JOIN PatMeds ON PatientID.PatientAccountID = PatMeds.PatientAccountID , PatMeds INNER JOIN MedSample ON PatMeds.ndc_id = MedSample.ndc_id WHERE ( MedSample.brand_name = "SEREVENT" or ( ( MedSample.brand_name LIKE "VENTOLIN*" or MedSample.brand_name LIKE "*[!A-Z0-9]VENTOLIN*" ) ) ) GROUP by MedSample.brand_name , Effectiveness.PatLaterVisit pivot PatientID.PatientLastName ;

Note that I substituted Ventolin at random because Flovent has no data for this example. By clicking on the Graph style response and choosing either Z-clustered or horizontal Z-Clustered style, you can get a graph that should go over very well in the demo!

To focus in only on the visits from 2 through 4, you would change the graph filter to read V<5. If you wanted to zoom in on particular patients, you could also use the Legend Pattern control (widen the graph form horizontally to get to this control), entering a pattern like [H-N]* -- to see Harville through Namaeth only.

An even better solution might be to have the Effective query run off an earlier crosstab which generated a table of Change values for all MedSamples, rather than hard-coding the "sGAWpp" as in the above example.

We received this in reply. . .

I'm not sure if I'm reading the graph right... what I got the first time was all the individual people, with their reading graphed... a little messier than what I wanted. What I really needed was the average of all the people together, graphed by visit number for each med, either a line or bar chart. I tried changing things... my query now reads:

Show the average change for Serevent and Ventolin by brand_name for the fourth PatLaterVisit.

This gives me two nice bars, one for Serevent, one for Ventolin. What I would like to really show is two sets, one for the second visit, and one for the fourth. I tried a query like:
Show the average change for Serevent and Ventolin by brand_name for the second and fourth PatLaterVisit.
However, it didn't understand my question. I suspect it is something simple... any ideas?

Also, I suspect I can go into the phrase editor, and add some things, like instead of PatLaterVisit, I can substitute the words patient visit. I would also like to add the phrase "Percent of predicted sGaw difference" (Which is really what we are plotting here) to represent the word change. Also the word medication for brand_name. This will make the statement very readable to Doctors looking like:
Show the average percent of predicted sGaw difference for Serevent and Ventolin by medication for the second and fourth visit
That will make this extremely dynamic! I think I'm starting to figure out what I'm doing here... just let me know if I'm going off the beaten path here on the phrase changes.

I was also able to add the diagnosis by adding the words "Where the PrimaryDiag = 493.00" at the end. PrimaryDiag is an existing field in the patpfts file. I may be better off adding it to the effectiveness query.

Thanks for your help....

And our reply. . .

You can add phrases to the phrase table very easily. I added the following:
When I Type:     I Really Mean
medication     brand_name
patient visit     PatLaterVisit
predicted sGaw difference     change

You can't add "Percent of predicted sGaw difference" because of a length limit. Normally this feature is used to provide short triggers for long phrases, not the other way around!

You don't have to add primary diagnosis => primarydiag, ELF is smart enough to figure this one out.

Now "show the average predicted sGaw difference for Ventolin and Serevent where patient visit < 5 and primary diagnosis = 493.0 by medication and patient visit" gives you almost what you want. If you use straight graph mode to display this it looks a little odd because ELF decides to graph the visit number. The thing to do is use the new Worksheet mode. Click More Options and then click the header of the AVG OF CHANGE column. Then click Graph Selected Columns. You get a graph of the values for visits 2 3 and 4. Now if you want to exclude visit 3, just click the Edit button and type <>3 in the criteria box under PatLaterVisit. (If you do this you should also add Ascending to the Sort box for brand_name to preserve the ordering.) Close and Save the query and click Graph Selected Columns again.

As a final touch, you might want to click the "More" button in the lower right corner. Now you can click Title off, then back on, and add a title such as "Percent of predicted sGaw difference" to the chart. If you select Graph from Grid/Graph/Split and click Footer off, this would make a nice slide.

Our beta tester was working with Access ELF, but here's a view of the same data using VB ELF, release 1.2. Notice the dashed lines and the trackball in the bottom right corner? Yes, these are fully interactive rotatable graphs.

Last Updated: August, 2009