Connecting a Database to a Spreadsheet to Pull Data

If you really want to learn about patient flow as it relates to practice management there is no greater technology than data mining and to do it yourself the easiest way is to connect your database to a spreadsheet.

Half of the article is a highly technical discussion of how to connect an Excel Pivot Table to a SQL database for constant information updates intended for those with moderate to advanced skills in Excel and basic skills in Access or other databases. Data mining is a method to discover hidden information or patterns in a large data bank. If you’re in a large hospital you probably already have someone that does this. If you’re in a small clinic and completely unfamiliar with databases you’re probably going to need some help. This entry shows one way that a lay-user can data mine their database (db) to find patterns. And now a couple of quick warnings; I am a self taught computer-geek and use this every day but am no expert. If you’re unfamiliar with this technology get professional help. Make sure you don’t go into you’re database with a username or password that will let you change data. Use a read-only name & password so you don’t do any damage. Never download patient names or street addresses just in case of a security breach. Finally, a database can still be hacked with read-only status so protect it like a normal password.

Now for some fun.

Any EMR system is a database and has “forms” that you see but in the background are “tables”. The tables are what hold the mountains of data for every field in the forms. The purpose of data mining is to extract and reorganize data from the tables. Usually you’re going to summarize the data somehow (count pateintID’s, calculate the difference in date or time stamps) and display it for populations of patients. Finding the data will be done in MS-Access. Summarizing it will be done in MS-Excel using Pivot tables. To start data mining:

Contact you’re EMR provider and get a read-only username & password to you’re database

Go explore – see what fields are in the database and what might be useful. You’ll find date and time stamps attached to many “rows” (records or entries). Table will hopefully be marked with the names you’ll understand (Appts for appointments, Pateint will often hold patient demographic info). Table that start with LU_ are called look-up tables and hold the drop down list choice that will be seen in various forms. To start pulling the information:

Using MS-Access link the tables from you’re database. Under Tables select New>Link Choose the db name and then Select All.

Depending on security you may need help from your EMR provider
Consider creating a Universal Data Link (.udl) file to hold the password

Create a query. A simple query does not change you’re data – it just takes it from the tables and temporarily re-organizes it. To count the number of active patients in the last year you could go to the table that hold patient demographic data. To create a query:

Add Tables to the query (right click in the top of the query field and add the table)
Drop the fields you want to summarize in the query columns. If you hit the ∑ symbol summarizing choices will be added. Usually you leave this at GroupBy. In some instances you may want to change it to Where. The Where clause will allow you to limit the data. For instance, you could limit PatientID’s only to records created after a certain data. Finally use one query to limit another. For instance create you’re first query with limits (say to limit all appointments to a certain appointment type). The create a second query with the data you want to summarize (usually a field to count patients or calculate dates) and link the same field from the first query (which limited to the appoint type in this example). The result, in this example, is that you will see all the patients that had a certain appointment type with the dates showing. In the picture below you can see the linked fields.

There are entire books written on creating queries so I’m going to stop here about the query. If you need more information there is plenty available on the web. The more powerful step of data mining is to import it into Excel. Before you leave Access however, choose View>SQL then cut and paste the SQL statement (that is in the background of you’re Access database) for the pivot table. Past it into notepad or Word somewhere that you can find it.

To link an Access Query to you’re SQL Database first open MS-Excel and choose Data>Pivottable. The Pivottable wizard will come up. If you’ve imported the list directly into Excel highlight the data first then open the Pivottable wizard.

But to have constantly updated data choose the “External data source” option. Then choose Next >> Get Data. At this step, if you haven’t already needed help, you will the first time. Under the database tab click Browse and find the database that holds you’re EMR data and select OK. If you’ve created a UDL you can choose that instead (which hold a permanent link to the datasource). Getting the Pivottable connected the first time can be difficult because of network security but once connect you will see the Query Wizard. The link to Query Wizard is a good one if you’re having trouble with this step.

Once in the Query Wizard choose the table and fields that you want to be connected to the Pivot Table. If, for instance, you want to know the wait time for an appointment from the Appt table choose PatientID, DateCreate, DateAppt and ApptType. Then keep hitting next until a Pivot Table is created. If you have you’re SQL Statement stored somewhere just choose any field and keep hitting next until you get to “What would you like to do next?” and select View Data or Edit query in MS Query which will bring up the following screen:
From here choose the SQL tab and paste your SQL statement in. When Access translates into SQL for some stupid reason it adds dbo_ to every table name. You’ll have to erase each of them. There are several other operators that have to be changed when in MS Query compared to MS Access but for simple queries everything should work. If not, MS Query usually points the way to translate. Convert to SQL (here’s a tutorial)
Once you’re done with MS Query simply close it and choose next then you’re pivot table will appear.

If you’ve made it this far in the lesson you’re a complete geek and I thank you for staying with it. The Pivot Table is an extremely power tool for analyzing data that I’ll cover in on another day. To start, however, try dropping the date fields into the “Row Fields” then choose “Group By” to sort by year and quarter. Then drop whatever it is you’re counting (usually patientID’s) into the data field and a descriptor (such as ApptType) into the Column fields. Right click on the data field to modify how the data is summarized and how the numbers are formatted. This is the beginning of data mining.