Notifications
Clear all
Topic starter
We receive spreadsheets from multiple insurance companies with a list of all patients assigned to our physicians. Our operations department needs to see when the last appointment and next appointment date for these patients are so that we can make sure to get the patients in to close the measures. Someone is currently looking up the patients manually and pulling their last appt and next appt data, typing it into the spreadsheet. Each spreadsheet is different and contains different fields. There are potentially hundreds of patients per spreadsheet. Has anyone found a way to automate or at least speed up this type of process. If they had our patient id's, it would be a lot easier. They usually send us First name, last name, dob, and sometimes insurance id.
I have done things in the past to patient match using a vlookup of lastname & dob and a second vlookup of insurance Id and dob, but this is very labor intensive and requires dumping a list of all patients to a tab in the spreadsheet.
I thought about maybe exporting them to CSV and running the CSV through Mirth or Qvera and have it do a SQL query for each patient and dump the CSV out with 2 extra fields.
If anyone is performing something similar to this please let me know how you do it.
Posted : March 22, 2017 8:56 am
Very doable. I work in either Crystal or Access. The entire issue is what information is furnished to you. As the data in provided as a spreadsheet, dealing with it is very straightforward. The probability of matches forming an index of lastname/firstname/DOB is very small and this could easily be checked. If you have insurance ID, you are home free. Extracting the data needed is straightforward once you Excel table is linked. I would be interested in working on the project with you, creating a model you can use. Contact me directly if you would like to pursue this.
Posted : March 22, 2017 11:14 am
Topic starter
Thank you for the offer however I was mainly looking for input on how people have tackled this type of challenge before. For example, what tools were used, I am not looking for someone to develop it for me as I can write code in a few languages myself.
For example, it would be possible to use excel with VBA database integration to lookup the data but that would be both tedious and would probably cause issues due to the variability in the format of the input data. In the past I have done this with a SQL query which dumped all patient demographic data from the system (or a subset of it) to a second worksheet and then used vlookup on concatenated columns (last name/dob, insurance id/dob, etc) to find the proper patient. It is not my favorite way of doing it.
Since I posted this a few hours ago now I have actually written a Mirth channel which I think I will end up using once I tweak it a bit more. The channel I just wrote takes the whole csv file as an input, loops through each line and looks up the patient (just on one criteria so far) and adds columns for the missing patient data and writes it back out to a different folder.
Posted : March 22, 2017 12:55 pm
